{"id":268,"date":"2009-03-27T10:17:24","date_gmt":"2009-03-27T09:17:24","guid":{"rendered":"http:\/\/hslomka.de\/techno\/?p=5"},"modified":"2009-03-27T10:17:24","modified_gmt":"2009-03-27T09:17:24","slug":"freier-speicherplatz-in-saporacle-tablespaces","status":"publish","type":"post","link":"https:\/\/techno.slomka.biz\/?p=268","title":{"rendered":"Freier Speicherplatz in SAP\/Oracle Tablespaces"},"content":{"rendered":"

Dieser Post erkl\u00e4rt die Ausgabe der Gr\u00f6\u00dfe und des freien Speicherplatzes von Tablespaces f\u00fcr Oracle Datenbanken mit Hilfe von SQL Befehlen.<\/p>\n

Beschreibung<\/strong>
\nF\u00fcr den Oracle Datenbankadministrator ist die Information \u00fcber die Gr\u00f6\u00dfe der Tablespaces und des freien Speicherplatzes in Ihnen essentiell wichtig. Das SQL Skript erzeugt eine formatierte und sortierte Ausgabe der gesamten Gr\u00f6\u00dfe, des freien Speicherplatzes und der Benutzung in Prozent.<\/p>\n

Hier k\u00f6nnen sie das zugeh\u00f6rige SQL_Skript herunterladen: ts1_free.sql<\/a><\/p>\n

SQL Statement<\/strong><\/p>\n

tti \"Tablespace Auslastung\"
\ncol \"MB frei\" format 999999.99
\ncol \"MB gesamt\" format 999999.99
\ncol Tablespace format a15<\/code><\/p>\n

select ddf.tablespace_name “Tablespace”,
\nddf.bytes\/1024\/1024 “MB gesamt”, sum(dfs.bytes)\/1024\/1024 “MB frei”,
\nround((ddf.bytes-sum(dfs.bytes))*100\/ddf.bytes) “Used%”<\/p>\n

from dba_free_space dfs,<\/p>\n

<<\/p>\n

div class=”command2″>(select tablespace_name, sum(bytes) bytes
\nfrom dba_data_files
\ngroup by tablespace_name ) ddf
\nwhere dfs.tablespace_name = ddf.tablespace_name
\nand ddf.tablespace_name like UPPER(‘%’)
\ngroup by ddf.tablespace_name, ddf.bytes order by “Used%”;<\/p>\n

Beispiel Ausgabe<\/strong><\/p>\n\n\n\n
Tue May 28<\/td>\nTablespace Auslastung<\/td>\npage 1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Tablespace<\/th>\nMB gesamt<\/th>\nMB frei<\/th>\nUsed%<\/th>\n<\/tr>\n
PSAPLOADD<\/td>\n 49.99<\/td>\n 49.86<\/td>\n 0<\/td>\n<\/tr>\n
PSAPROLLBIG<\/td>\n 1999.99<\/td>\n 1999.98<\/td>\n 0<\/td>\n<\/tr>\n
PSAPTEMP<\/td>\n 1623.99<\/td>\n 1623.98<\/td>\n 0<\/td>\n<\/tr>\n
PSAPLOADI<\/td>\n 20.00<\/td>\n 19.86<\/td>\n 1<\/td>\n<\/tr>\n
PSAPPROTI<\/td>\n 309.99<\/td>\n 269.33<\/td>\n 13<\/td>\n<\/tr>\n
PSAPEL46CI<\/td>\n 400.00<\/td>\n 329.92<\/td>\n 18<\/td>\n<\/tr>\n
PSAPPROTD<\/td>\n 449.99<\/td>\n 319.26<\/td>\n 29<\/td>\n<\/tr>\n
PSAPSOURCED<\/td>\n 349.99<\/td>\n 250.24<\/td>\n 29<\/td>\n<\/tr>\n
PSAPDOCUI<\/td>\n 109.99<\/td>\n 70.38<\/td>\n 36<\/td>\n<\/tr>\n
SYSTEM<\/td>\n 749.99<\/td>\n 453.02<\/td>\n 40<\/td>\n<\/tr>\n
PSAPROLL<\/td>\n 799.99<\/td>\n 462.80<\/td>\n 42<\/td>\n<\/tr>\n
PSAPUSER1D<\/td>\n 199.98<\/td>\n 101.30<\/td>\n 49<\/td>\n<\/tr>\n
PSAPUSER1I<\/td>\n 299.98<\/td>\n 147.70<\/td>\n 51<\/td>\n<\/tr>\n
PSAPDOCUD<\/td>\n 149.99<\/td>\n 72.02<\/td>\n 52<\/td>\n<\/tr>\n
PSAPDDICI<\/td>\n 619.99<\/td>\n 263.89<\/td>\n 57<\/td>\n<\/tr>\n
PSAPCLUI<\/td>\n 199.99<\/td>\n 83.59<\/td>\n 58<\/td>\n<\/tr>\n
PSAPDDICD<\/td>\n 949.99<\/td>\n 349.14<\/td>\n 63<\/td>\n<\/tr>\n
PSAPSOURCEI<\/td>\n 249.99<\/td>\n 90.32<\/td>\n 64<\/td>\n<\/tr>\n
PSAPCLUD<\/td>\n 1323.98<\/td>\n 420.91<\/td>\n 68<\/td>\n<\/tr>\n
PSAPPOOLD<\/td>\n 5110.52<\/td>\n 1631.48<\/td>\n 68<\/td>\n<\/tr>\n
PSAPES46CD<\/td>\n 9379.98<\/td>\n 2326.44<\/td>\n 75<\/td>\n<\/tr>\n
PSAPPOOLI<\/td>\n 3335.91<\/td>\n 764.48<\/td>\n 77<\/td>\n<\/tr>\n
PSAPES46CI<\/td>\n 5063.98<\/td>\n 1035.93<\/td>\n 80<\/td>\n<\/tr>\n
PSAPSTABD<\/td>\n 7544.81<\/td>\n 1513.48<\/td>\n 80<\/td>\n<\/tr>\n
PSAPBTABI<\/td>\n 18239.93<\/td>\n 3292.70<\/td>\n 82<\/td>\n<\/tr>\n
PSAPEL46CD<\/td>\n 1249.99<\/td>\n 207.78<\/td>\n 83<\/td>\n<\/tr>\n
PSAPSTABI<\/td>\n 5306.58<\/td>\n 699.70<\/td>\n 87<\/td>\n<\/tr>\n
PSAPBTABD<\/td>\n 29429.88<\/td>\n 2672.33<\/td>\n 91<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

Dieser Post erkl\u00e4rt die Ausgabe der Gr\u00f6\u00dfe und des freien Speicherplatzes von Tablespaces f\u00fcr Oracle Datenbanken mit Hilfe von SQL Befehlen. Beschreibung F\u00fcr den Oracle Datenbankadministrator ist die Information \u00fcber die Gr\u00f6\u00dfe der Tablespaces und des freien Speicherplatzes in Ihnen essentiell wichtig. Das SQL Skript erzeugt eine formatierte und sortierte Ausgabe der gesamten Gr\u00f6\u00dfe, des freien Speicherplatzes und der Benutzung in Prozent. Hier k\u00f6nnen sie das zugeh\u00f6rige SQL_Skript herunterladen: ts1_free.sql SQL Statement tti “Tablespace Auslastung” col “MB frei” format 999999.99 col “MB gesamt” format 999999.99 col Tablespace format a15 select ddf.tablespace_name “Tablespace”, ddf.bytes\/1024\/1024 “MB gesamt”, sum(dfs.bytes)\/1024\/1024 “MB frei”, round((ddf.bytes-sum(dfs.bytes))*100\/ddf.bytes) “Used%” from dba_free_space dfs, < div class=”command2″>(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) ddf where dfs.tablespace_name = ddf.tablespace_name and ddf.tablespace_name like UPPER(‘%’) group by ddf.tablespace_name, ddf.bytes order by “Used%”; Beispiel Ausgabe Tue May 28 Tablespace Auslastung page 1 Tablespace MB gesamt MB frei Used% PSAPLOADD 49.99 49.86 0 PSAPROLLBIG 1999.99 1999.98 0 PSAPTEMP 1623.99 1623.98 0 PSAPLOADI 20.00 19.86 1 PSAPPROTI 309.99 269.33 13 PSAPEL46CI 400.00 329.92 18 PSAPPROTD 449.99 319.26 29 PSAPSOURCED 349.99 250.24 29 PSAPDOCUI 109.99 70.38 36 SYSTEM 749.99 453.02 40 PSAPROLL 799.99 462.80 42 PSAPUSER1D 199.98 101.30 49 PSAPUSER1I 299.98 147.70 51 PSAPDOCUD 149.99 72.02 52 PSAPDDICI 619.99 263.89 57 PSAPCLUI 199.99 83.59 58 PSAPDDICD 949.99 349.14 63 PSAPSOURCEI 249.99 90.32 64 PSAPCLUD 1323.98 420.91 68 PSAPPOOLD 5110.52 1631.48 68 PSAPES46CD 9379.98 2326.44 75 PSAPPOOLI 3335.91 764.48 77 PSAPES46CI 5063.98 1035.93 80 PSAPSTABD 7544.81 1513.48 80 PSAPBTABI 18239.93 3292.70 82 PSAPEL46CD 1249.99 207.78 83 PSAPSTABI 5306.58 699.70 87 PSAPBTABD 29429.88 2672.33 91<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,13,15,22],"tags":[77,99],"class_list":["post-268","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracle-datenbank","category-sap","category-tablespaces","tag-oracle-db-administration","tag-tablespace"],"_links":{"self":[{"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=\/wp\/v2\/posts\/268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=268"}],"version-history":[{"count":0,"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=\/wp\/v2\/posts\/268\/revisions"}],"wp:attachment":[{"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techno.slomka.biz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}