查表空间使用率使用脚本

SELECT  
d.tablespace_name "Name", 
d.status "Status", 
d.contents "Type", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),‘99G999G990D900‘) "Size_MB",
TO_CHAR(NVL(a.max_bytes / 1024 / 1024, 0),‘99G999G990D900‘) "MAX_Size_MB",  
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, ‘99G999G990D900‘) "Used_MB", 
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.max_bytes * 100, 0), ‘990D00‘) "Useds%" 
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes, sum(case when AUTOEXTENSIBLE = ‘NO‘ then bytes when AUTOEXTENSIBLE = ‘YES‘ then MAXBYTES end ) max_bytes from dba_data_files group by tablespace_name
union all 
select tablespace_name, sum(bytes) bytes, sum(case when AUTOEXTENSIBLE = ‘NO‘ then bytes when AUTOEXTENSIBLE = ‘YES‘ then MAXBYTES end ) max_bytes from dba_temp_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes) bytes 
from dba_free_space group by tablespace_name) f  
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = f.tablespace_name(+)
order by 3,1;



Name                           Status    Type      Size_MB         MAX_Size_MB     Used_MB         Useds%
------------------------------ --------- --------- --------------- --------------- --------------- -------
xxxxxx_01 c                    ONLINE    PERMANENT      30,720.000      63,487.000      19,557.938   30.81
Cccccc                         ONLINE    PERMANENT       1,024.000      32,767.984         713.750    2.18
fffffff                        ONLINE    PERMANENT     277,896.000     425,974.938     231,786.875   54.41
ffCLMINDEX                     ONLINE    PERMANENT      43,007.000      43,007.984      27,361.688   63.62
SYSAUX                         ONLINE    PERMANENT         590.000      32,767.984         551.313    1.68
SYSTEM                         ONLINE    PERMANENT       1,024.000      32,767.984         543.625    1.66
USERS                          ONLINE    PERMANENT       2,047.000      32,767.984       1,214.313    3.71
CLffffB_TEMP01                 ONLINE    TEMPORARY      20,480.000      20,480.000      20,480.000  100.00
TEMP                           ONLINE    TEMPORARY      30,720.000      32,767.984      30,720.000   93.75
UNDOTBS1                       ONLINE    UNDO           30,720.000      32,767.984         373.250    1.14

10 rows selected.