Freier und genutzter Speicherplatz eines ORACLE Tablespaces

von Tobias Redmann

Mit den folgenden SQL-Statements wird der freie und schon genutzte Speicherplatz, sowie der Füllstand in Prozent, innerhalb eines Tablespace in einer ORACLE Datenbank ermittelt.

Der maximal mögliche Speicherplatz eines Tablespaces wird dabei nicht berücksichtigt, sondern nur schon allokierter.

Diese SQL-Statement ermittelt den freien Speicherplatz aller Tablespaces.

SELECT
dfs.tablespace_name
, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte
, SUM(dfs.bytes) / (1024 * 1024 * 1024) FreierSpeicher_In_GByte
FROM dba_free_space dfs
WHERE 1=1
GROUP BY dfs.tablespace_name;

SELECT

dfs.tablespace_name

, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte

, SUM(dfs.bytes) / (1024 * 1024 * 1024) FreierSpeicher_In_GByte

FROM dba_free_space dfs

WHERE 1=1

GROUP BY dfs.tablespace_name;

Diese SQL-Statement ermittelt den gesamt zur Verfügung stehenden Speicherplatz aller Tablespaces von schon allokiertem Speicher. Der maximal mögliche Speicherplatz wird dabei nicht berücksichtigt.

SELECT
ddf.tablespace_name
, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte
, SUM(ddf.bytes) / (1024 * 1024*1024) GesamtSpeicher_In_GByte
FROM dba_data_files ddf
WHERE 1=1
GROUP BY ddf.tablespace_name;

SELECT

ddf.tablespace_name

, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte

, SUM(ddf.bytes) / (1024 * 1024*1024) GesamtSpeicher_In_GByte

FROM dba_data_files ddf

WHERE 1=1

GROUP BY ddf.tablespace_name;

Werden die zuvor aufgeführten SQL-Statements miteinander kombiniert, erhält man eine schöne Auflistung der Speicherbelegung aller in der Datenbank vorhandener Tablespaces.

Den verwendeten Speicher kann man nicht direkt aus dem DataDict auslesen, sondern muss diesen aus dem Gesamtspeicherplatz und dem schon belegten Speicherplatz eines Tablespaces berechnen.

SELECT
FreierspeicherTbl.tablespace_name
,GesamtSpeicherTbl.GesamtSpeicher_In_MByte
,FreierspeicherTbl.FreierSpeicher_In_MByte
,(
GesamtSpeicherTbl.GesamtSpeicher_In_MByte -
FreierspeicherTbl.FreierSpeicher_In_MByte
) VerwendeterSpeicher_In_MByte
FROM
(
SELECT
dfs.tablespace_name
, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte
FROM dba_free_space dfs
WHERE 1=1
GROUP BY dfs.tablespace_name
) FreierspeicherTbl
,(
SELECT
ddf.tablespace_name
, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte
FROM dba_data_files ddf
WHERE 1=1
GROUP BY ddf.tablespace_name
GesamtSpeicherTbl
WHERE 1=1
AND FreierspeicherTbl.tablespace_name = GesamtSpeicherTbl.tablespace_name;

SELECT

FreierspeicherTbl.tablespace_name

,GesamtSpeicherTbl.GesamtSpeicher_In_MByte

,FreierspeicherTbl.FreierSpeicher_In_MByte

GesamtSpeicherTbl.GesamtSpeicher_In_MByte -

FreierspeicherTbl.FreierSpeicher_In_MByte

) VerwendeterSpeicher_In_MByte

FROM

(

SELECT

dfs.tablespace_name

, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte

FROM dba_free_space dfs

WHERE 1=1

GROUP BY dfs.tablespace_name

) FreierspeicherTbl

SELECT

ddf.tablespace_name

, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte

FROM dba_data_files ddf

WHERE 1=1

GROUP BY ddf.tablespace_name

GesamtSpeicherTbl

WHERE 1=1

AND FreierspeicherTbl.tablespace_name = GesamtSpeicherTbl.tablespace_name;

Mit einfacher Mathematik kann nun noch der Füllstand in Prozent des belegten Speichers innerhalb des Tablespaces ermittelt werden.

SELECT
FreierspeicherTbl.tablespace_name
,GesamtSpeicher_In_MByte
,FreierSpeicher_In_MByte
,(GesamtSpeicherTbl.GesamtSpeicher_In_MByte -
FreierspeicherTbl.FreierSpeicher_In_MByte) VerwendeterSpeicher_In_MByte
,Round((
(
GesamtSpeicherTbl.GesamtSpeicher_In_MByte -
FreierspeicherTbl.FreierSpeicher_In_MByte
) * 100 / GesamtSpeicher_In_MByte
)
,2) Fuellstand_in_Prozent
FROM
(
SELECT
dfs.tablespace_name
, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte
FROM dba_free_space dfs
WHERE 1=1
GROUP BY dfs.tablespace_name
) FreierspeicherTbl
,(
SELECT
ddf.tablespace_name
, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte
FROM dba_data_files ddf
WHERE 1=1
GROUP BY ddf.tablespace_name
) GesamtSpeicherTbl
WHERE 1=1
AND FreierspeicherTbl.tablespace_name = GesamtSpeicherTbl.tablespace_name
-- AND TABLESPACE_NAME IN ('TBL_BESSERDICH')
ORDER by Fuellstand_in_Prozent DESC;

SELECT

FreierspeicherTbl.tablespace_name

,GesamtSpeicher_In_MByte

,FreierSpeicher_In_MByte

,(GesamtSpeicherTbl.GesamtSpeicher_In_MByte -

FreierspeicherTbl.FreierSpeicher_In_MByte) VerwendeterSpeicher_In_MByte

,Round((

(

GesamtSpeicherTbl.GesamtSpeicher_In_MByte -

FreierspeicherTbl.FreierSpeicher_In_MByte

) * 100 / GesamtSpeicher_In_MByte

)

,2) Fuellstand_in_Prozent

FROM

(

SELECT

dfs.tablespace_name

, SUM(dfs.bytes) / (1024 * 1024) FreierSpeicher_In_MByte

FROM dba_free_space dfs

WHERE 1=1

GROUP BY dfs.tablespace_name

) FreierspeicherTbl

SELECT

ddf.tablespace_name

, SUM(ddf.bytes) / (1024 * 1024) GesamtSpeicher_In_MByte

FROM dba_data_files ddf

WHERE 1=1

GROUP BY ddf.tablespace_name

) GesamtSpeicherTbl

WHERE 1=1

AND FreierspeicherTbl.tablespace_name = GesamtSpeicherTbl.tablespace_name

-- AND TABLESPACE_NAME IN ('TBL_BESSERDICH')

ORDER by Fuellstand_in_Prozent DESC;

Der Temp-Tablespace wurde nicht in den oben aufgeführten SQL-Statements berücksichtigt, da die Lesbarkeit stark darunter leiden würden.

Um auch hier alle wichtigen Werte zu ermitteln kann folgendes Statement ausgeführt werden.

SELECT
dtfs.tablespace_name
,(
dtfs.tablespace_size/ (1024 * 1024)
) GesamtSpeicher_In_MByte
,(
dtfs.free_space / (1024 * 1024)
) FreierSpeicher_In_MByte
,(
(
dtfs.tablespace_size - dtfs.free_space
) / (1024 * 1024)
) VerwendeterSpeicher_In_MByte
,Round
(
(
(
dtfs.tablespace_size -
dtfs.free_space
) * 100 / dtfs.tablespace_size
),2
) Fuellstand_in_Prozent
FROM dba_temp_free_space dtfs;

SELECT

dtfs.tablespace_name

dtfs.tablespace_size/ (1024 * 1024)

) GesamtSpeicher_In_MByte

dtfs.free_space / (1024 * 1024)

) FreierSpeicher_In_MByte

(

dtfs.tablespace_size - dtfs.free_space

) / (1024 * 1024)

) VerwendeterSpeicher_In_MByte

,Round

(

dtfs.tablespace_size -

dtfs.free_space

) * 100 / dtfs.tablespace_size

),2

) Fuellstand_in_Prozent

FROM dba_temp_free_space dtfs;

Der Vollständigkeit halber muss jedoch noch der theoretisch mögliche Speicherplatz eines Tablespaces abgefragt werden. Das ist der Speicher der noch nicht allokiert wurde, jedoch theoretisch, sofern die Festplatte die Größe aufnehmen kann, noch in dem Tablespace aufgenommen werden kann.

So kann man das Ergebnis in ein paar Sätzen erläutern.
In dem Tablespace TBL_TEST „tablespace_name“ wurden schon 11,5 MByte „used_MByte“ genutzt. Im Vorfeld wurden 18,5 MByte „allocated_MByte“ Speicher allokiert von denen noch 7 MByte „free_from_allocated_In_MByte“ zur Verfügung stehen. Der Tablespace könnte theoretisch weitere 32628,5 MByte „theoretisch_wachstum_MByte“ Daten aufnehmen.

Select
tab2.tablespace_name
,tab1.free_from_allocated_In_MByte
,tab2.allocated_MByte
,(tab2.allocated_MByte - tab1.free_from_allocated_In_MByte) used_MByte
,tab2.max_wachstum_MByte - (tab2.allocated_MByte - tab1.free_from_allocated_In_MByte) theoretisch_wachstum_MByte
FROM
(SELECT
dfs.tablespace_name
, round(SUM(dfs.bytes) / (1024 * 1024)) free_from_allocated_In_MByte
FROM dba_free_space dfs
WHERE 1=1
GROUP BY dfs.tablespace_name
) tab1
,
(SELECT
ddf.tablespace_name
, SUM(ddf.bytes) / (1024 * 1024) allocated_MByte
, round(sum(ddf.maxbytes) / (1024 * 1024)) max_wachstum_MByte
FROM dba_data_files ddf
WHERE 1=1
GROUP BY ddf.tablespace_name
) tab2
Where 1=1
and tab1.tablespace_name = tab2.tablespace_name;

Select

tab2.tablespace_name

,tab1.free_from_allocated_In_MByte

,tab2.allocated_MByte

,(tab2.allocated_MByte - tab1.free_from_allocated_In_MByte) used_MByte

,tab2.max_wachstum_MByte - (tab2.allocated_MByte - tab1.free_from_allocated_In_MByte) theoretisch_wachstum_MByte

FROM

(SELECT

dfs.tablespace_name

, round(SUM(dfs.bytes) / (1024 * 1024)) free_from_allocated_In_MByte

FROM dba_free_space dfs

WHERE 1=1

GROUP BY dfs.tablespace_name

) tab1

(SELECT

ddf.tablespace_name

, SUM(ddf.bytes) / (1024 * 1024) allocated_MByte

, round(sum(ddf.maxbytes) / (1024 * 1024)) max_wachstum_MByte

FROM dba_data_files ddf

WHERE 1=1

GROUP BY ddf.tablespace_name

) tab2

Where 1=1

and tab1.tablespace_name = tab2.tablespace_name;

6 KOMMENTARE

2013

von WillAnonymBleiben

Danke für die Zusammenfassung. Sehr schön geschrieben.

2015

von Robert

Im dritten Select fehlt eine schließende Klammer vor "GesamtSpeicherTbl" und die "Where 1=1" sollte man sich doch sparen können - zumindest kann ich darin keinen Sinn erkennen. Es gibt übrigens Code-PlugIns, bei denen man den Code Kopieren kann und dieser dann ohne Zeilennummer gepasted werden kann. Zumindest ist mein Verständnis von veröffentlichtem Code oder SQL, dass man diesen zur Nutzung anbeitet.... (In dem CMS, welches ich nutze, wird dieses "PlugIn" verwendet: http://alexgorbatchev.com/SyntaxHighlighter/ da funktioniert das mit Copy & Paste per Doppelklick) Aber trotzdem vielen Dank für die hilfreichen SQLs :)

2015

von Karsten Besserdich

Hallo Robert, danke für deine Hinweise! Habe ich gleich umgesetzt. Zum Thema Where 1=1: 1. Ich erkenne sofort das ich das geschrieben habe :-) Bei mehreren AND Bedingungen kann ich einfach jede Bedingung auskommentieren. Anbei ein kleines Beispiel. Es ist einfach umständlich im Statement 1 die erste Bedingung auszukommentieren. [sql] -- Statement 1 SELECT * FROM TEST WHERE BEDINGUNG1 = 5 AND BEDINGUNG2 = 5 -- Statement 2 SELECT * FROM TEST WHERE 1=1 -- AND BEDINGUNG1 = 5 AND BEDINGUNG2 = 5 [/sql]

2015

von Robert

Wow, das ist ja mal eine Reaktion - gleich alles umgesetzt :) Ah klar, leichter auskommentierbar ist es so natürlich... Kann es sein, dass die Anzeige des verfügbaren Platzes sich auf die bereits einmal genutzten Extents beschränkt und bislang ungenutzter Platz nicht angezeigt wird? (Zumindest scheint es bei mir so zu sein....)

2017

von Björn Schulz

Super Zusammenfassung, Vielen Dank!

2017

von Martin W.

Danke - die Statementes waren gut erklärt und sehr hilfreich

SCHREIBEN SIE EINEN KOMMENTAR

Sie sind hier: