Tabellengröße abfragen

Wie findet man die Größe mehrerer Tabellen heraus, die in mehreren Schemata liegen?

Die Anforderung lautete: “Ermitteln Sie doch bitte mal den Festplattenplatz den wir einsparen können, wenn wir die Tabelle “XY“ in Schema “AB“ und die Tabellen, die ich hier in dem Excelsheet notiert habe, löschen werden. Und schon hat man 68 Tabellennamen und 6 Schemata im Excelformat.

Man kann sie nun die Zeit nehmen und per GUI die Tabellen suchen, sich die Tabellengröße anzeigen lassen, diese in das Excelsheet eintragen und die Größen summieren.

Oder man hat das entsprechende Select-Grant auf die Tabelle dba_segments und das richtige Statement.

Zunächst erstellt man eine Tabelle, in die man die Schemanamen und die Tabellennamen einfügen kann:

— Tabelle mit den Schemanamen und Tabellennamen,

— die berechnet werden sollen

CREATE TABLE create_table_sizes( owner VARCHAR2(30 BYTE) ,table_name VARCHAR2 (81 Byte) ); 

Anschließend erstellt man eine Tabelle, in der man die Ergebnisse speichert, falls man zu einem späteren Zeitpunkt noch mal darauf zugreifen können muss (z.B. wenn die Tabellen gelöscht wurden und das Excelsheet auf ominöse Art und Weise verloren gegangen ist ;-))

— Ergebnistabelle aus welcher die Daten — jederzeit wieder ausgelesen werden können

CREATE TABLE table_sizes ( owner VARCHAR2(30 BYTE) ,table_name VARCHAR2 (81 Byte) ,db_blocks NUMBER ,table_size NUMBER ,unit VARCHAR2 
(8 BYTE) ,table_space VARCHAR2 (30 BYTE) ,created DATE ,createdby VARCHAR2 (40 byte) );

Und dann wird es interessant: Die Ergebnistabelle wird gefüllt. Dabei erfragen wir von Oracle den Schemanamen, den Tabellennamen, die Blöcke, die angeforderte Größe in der entsprechenden Einheit und speichern natürlich das Abfragedatum und den Namen des Autors ab.

— Falls es interessant ist, wer die Statistik erstellt hat — (das wann wird automatisch eingefügt): — UNDEFINE created_by; — DEFINE created_by := ‚SBROEMER‘;

INSERT INTO table_sizes ( SELECT dse."owner" , dse."table" table_name , dse.db_blocks , ROUND(DECODE(SIGN(dse."size"/1073741824 -1) , -1, 
DECODE(SIGN(dse."size"/1048576 -1) , -1 , DECODE(SIGN(dse."size"/1024 -1) , -1, dse."size" , dse."size"/1024) , dse."size"/1048576 
, dse."size"/1073741824), 2) "TABLE_SIZE" , DECODE(SIGN(dse."size"/1073741824
-1) , -1, DECODE(SIGN(dse."size"/1048576 -1 ) , -1, 
DECODE(SIGN(dse."size"/1024 -1) , -1 ,' Byte' , ' Kb') , ' Mb') , 'Gb' ) "Unit" , dse."tablespace" table_space , SYSDATE , 
'&created_by' FROM ( SELECT ds.owner "owner" , ds.segment_name "table" , SUM(ds.BYTES) "size" , ds.blocks db_blocks 
, ds.tablespace_name "tablespace" FROM c ds , create_table_sizes dt WHERE segment_type in ('TABLE') AND ds.owner = dt.owner AND 
ds.segment_name = dt.table_name GROUP BY ds.owner, ds.segment_name, ds.blocks, ds.tablespace_name UNION SELECT ds.owner "owner" 
, ds.segment_name "table" , SUM(ds.BYTES) "size" , SUM(ds.blocks) db_blocks , 'n/a' "tablespace" FROM dba_segments ds 
, create_table_sizes dt WHERE segment_type in ('TABLE PARTITION', 'TABLE SUBPARTITION') AND ds.owner = dt.owner AND ds.segment_name 
= dt.table_name GROUP BY ds.owner, ds.segment_name ) dse );

COMMIT;

Zum guten Schluss lässt sich das Ergebnis schön sortiert nach Schema und Tabelle ausgeben:

SELECT * FROM table_sizes ORDER BY owner ASC, table_name ASC;

Bildnachweise:
Titelbild: ©Fotolia_60505744_S-ci