Free Space in Oracle- Tablespaces

Das folgende Skript zeigt die Speicherbelegung nicht nur nach Tablespace, sondern auch nach Datafile an:

Oracle Database: Free Space in Tablespaces
SET FEEDBACK OFF
SET PAGESIZE 2000
SET LINESIZE 260
 
SELECT   substr(df.tablespace_name,1,20) "Tablespace Name",
         substr(df.file_name,1,40)        "File Name",
         round(df.bytes/1024/1024,2)      "Size (M)",
         round(e.used_bytes/1024/1024,2)  "Used (M)",
         round(f.free_bytes/1024/1024,2)  "Free (M)",
         rpad(' '|| rpad ('X',
                          round(e.used_bytes*10/df.bytes,0), 'X')
                          ,11,'-')        "% Used"
FROM   DBA_DATA_FILES DF,
         (SELECT file_id,
                 sum(Decode(bytes,NULL,0,bytes)) used_bytes
          FROM dba_extents
          GROUP by file_id) E,
         (SELECT Max(bytes) free_bytes,
                 file_id
          FROM dba_free_space
          GROUP BY file_id) f
WHERE     e.file_id (+) = df.file_id
AND       df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;

 

Die Ausgabe kann beispielsweise wie folgt aussehen: 

Tablespace Name      File Name                                  Size (M)   Used (M)   Free (M) % Used
-------------------- ---------------------------------------- ---------- ---------- ---------- -----------
EXAMPLE              D:\APP\A\ORADATA\ORA11G\EXAMPLE01.DBF           100      77,31      19,94  XXXXXXXX--
EXAMPLE              D:\APP\A\ORADATA\ORA11G\EXAMPLE02.DBF            10                  9,94  ----------
FLASHBACK_T101       C:\TEMP\TEST.DBF                                200                199,94  ----------
SYSAUX               D:\APP\A\ORADATA\ORA11G\SYSAUX01.DBF         636,81     581,56      26,75  XXXXXXXXX-
SYSTEM               D:\APP\A\ORADATA\ORA11G\SYSTEM01.DBF            700     689,88       9,94  XXXXXXXXXX
UNDOTBS_02           C:\TEMP\UNDO02_01.DBF                           200      16,25     179,94  X---------
USERS                D:\APP\A\ORADATA\ORA11G\USERS01.DBF         1414,44     483,06     930,38  XXX-------

 

 

Noch einfacher ist das Monitoring von Speicherplatz in Temporary Tablespaces:

SQL
SELECT *
FROM   dba_temp_free_space;
 

 

Die Ausgabe zeigt die Speicherplatznutzung und den freien Speicherplatz: 

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 180355072       180355072  174063616