Tablespaces Füllgrad

Als zweites Beispiel sehen wir uns das Thema Tablespace Füllgrad an - hier gibt es leider viele Beispiele, die keine korrekten Ergebnisse liefern.

Teil 2: Tablespaces überwachen

Die einfache Lösung: DBA_FREE_SPACE anschauen, das war's. Oder vielleicht doch nicht?

DBA_FREE_SPACE hat mehrere Probleme

Wenn RECYCLEBIN=ON (die Default-Einstellung), dann kann diese View seeeeeeeehr langsam werden.
Wenn die Datafiles auf AUTOEXTEND sind, dann reicht die Info hier nicht.
(kein echtes Problem): Der Undo-Tablespace sollte hier ausgenommen werden, der tickt völlig anders und würde des öfteren zu Falschmeldungen führen.
Es kommt kein korrektes Ergebnis, wenn ein Tablespace überhaupt keine Objekte enthält (weil er gerade angelegt wurde) bzw. wenn der Tablespace wirklich bis auf den letzten Block voll ist.

Eine mögliche Lösung

WITH df AS (
	      SELECT tablespace_name, bytes, CASE maxbytes WHEN 0 THEN bytes ELSE maxbytes END AS maxbytes
	         FROM dba_data_files),
	     f AS (
	      SELECT tablespace_name, sum(bytes) as bytes_, sum(maxbytes) as maxbytes_, count(*) as files
	         FROM df
	         GROUP BY tablespace_name),
	     s AS (
	      SELECT tablespace_name, sum(bytes) as bytes_, count(*) as segs  
	            FROM dba_segments
	         GROUP BY tablespace_name
	         )
	SELECT f.tablespace_name,
	       ROUND(sum(f.bytes_)/1024/1024) AS curmb,
	       ROUND(sum(f.maxbytes_)/1024/1024) AS maxmb,
	       ROUND(nvl(sum(s.bytes_),0)/1024/1024) AS totalused,
	       ROUND(sum(f.maxbytes_ - nvl(s.bytes_,0))/1024/1024) AS totalfree,
	       ROUND(nvl(sum(s.bytes_),0)/sum(f.maxbytes_)*100,1) as upct
	   FROM f
	   LEFT OUTER JOIN s
	     ON f.tablespace_name = s.tablespace_name
	   JOIN dba_tablespaces ts
	     ON ts.tablespace_name = f.tablespace_name
	   WHERE contents = 'PERMANENT'
	   GROUP BY f.tablespace_name
	   ORDER BY 1;

Die Query gibt pro Tablespace die momentane Größe der Datafiles, die maximal mögliche Größe der Datafiles, die Summe der Größe aller Objekte im Tablespace, den (theoretisch) freien Platz und den derzeitigen Platzverbrauch in Prozenten an. DBA_FREE_SPACE bleibt außen vor. Der UNDO-Tablespace wird durch WHERE contents = 'PERMANENT' ausgeblendet.

Für eine Multitenant Datenbank muss man auf CDB Views zugreifen und die CON_ID berücksichtigen.

WITH df AS (SELECT con_id, tablespace_name, bytes, CASE maxbytes WHEN 0 THEN bytes ELSE maxbytes END AS maxbytes
             FROM cdb_data_files),
      f AS (SELECT con_id, tablespace_name, sum(bytes) as bytes_, sum(maxbytes) as maxbytes_, count(*) as files
              FROM df
             GROUP BY con_id, tablespace_name),
      s AS (SELECT con_id, tablespace_name, sum(bytes) as bytes_, count(*) as segs  
              FROM cdb_segments
             GROUP BY con_id, tablespace_name)
SELECT f.con_id, f.tablespace_name,
      ROUND(sum(f.bytes_)/1024/1024) AS curmb,
      ROUND(sum(f.maxbytes_)/1024/1024) AS maxmb,
      ROUND(nvl(sum(s.bytes_),0)/1024/1024) AS totalused,
      ROUND(sum(f.maxbytes_ - nvl(s.bytes_,0))/1024/1024) AS totalfree,
      ROUND(nvl(sum(s.bytes_),0)/sum(f.maxbytes_)*100,1) as upct
 FROM f LEFT OUTER JOIN s ON (f.tablespace_name = s.tablespace_name and f.con_id=s.con_id)
        JOIN cdb_tablespaces ts ON (ts.tablespace_name = f.tablespace_name and ts.con_id=f.con_id)
WHERE contents = 'PERMANENT'
GROUP BY f.con_id, f.tablespace_name
ORDER BY 1;

Breadcrumb