its-people Blog – Der Oracle Blog neben Beiträgen aus den Bereichen: Datenbanken, Business Intelligence & Datawarehouse, IT-Management Service & ERP-Beratung

Oracle – Tabellen eines Tablespaces anzeigen

Es war einmal wieder so weit:
Neulich kam eine Mail, dass der Tablespace dabei ist wegen Überfüllung die Arbeit einzustellen. Man sollte doch bitte prüfen, welche Tabellen von dem Tablespace gelöscht werden können.

Zunächst setzt dies natürlich voraus, dass man die Tabelle sys.dba_segments sehen darf.
Sofern man das darf, lässt man sich zunächst die Tabellen nach Relevanz, in diesem Falle die Größe, anzeigen und prüft mit gesundem Augenmaß, welche Tabelle gelöscht werden kann:
read more…

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;

Partitionen einer Tabelle in Oracle auslesen

Für meinen ersten Block hier wollte ich erstmal ein einfaches Thema ansprechen:
Das Auslesen von vorhanden Partitionen einer Tabelle.

Mir passierte es, dass ich bei einem Kunden saß und mir mit Toad die Partitionen einer Tabelle anschauen wollte.
Also erstmal über die GUI die Tabelle im entsprechenden Schema suchen und sich dann die Description anzeigen lassen. Anschließend ganz einfach auf den Tab “Partitions” wechseln.
Eigentlich ganz einfach. Wenn nun aber die GUI Probleme hat wieder ins Leben zurückzukehren und aufzutauen, weil bereits so viele Partitionen existieren, hört die Einfachheit auf.

Was tut man also in so einer Situation?
Richtig! SQLPLUS öffnen und per Hand abfragen:

– Für den User:
SELECT table_name
, partition_name
, partition_position
, tablespace_name
FROM user_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;

– Mit Blick auf die dba_tab_partitions:
SELECT table_owner
, table_name
, partition_name
, partition_position
, tablespace_name
FROM dba_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;