oracle处理temp表空间爆长的问题(四)
to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",
to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
'99999999.999') "Used (M)",
to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') "Used %"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_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(+);
Status Name Type Extent Man Total Size (M Used (M) Free (M) Used %
--------- ------------------------------ --------- ----------
ONLINE BMIS_DATA PERMANENT LOCAL 2046.000 1.000 2045.000 0.05
ONLINE CPAS_DATA PERMANENT LOCAL 1023.000 227.500 795.500 22.24
ONLINE INDX PERMANENT LOCAL 255.000 .063 254.938 0.02
ONLINE MOMAX_DATA PERMANENT LOCAL 13296.000 10785.500 2510.500 81.12
ONLINE MOPORTAL_DATA PERMANENT LOCAL 2046.000 733.063 1312.938 35.83
ONLINE MPAS_DATA PERMANENT LOCAL 3069.000 105.000 2964.000 3.42
ONLINE SMAIL_DATA PERMANENT LOCAL 1023.000 7.688 1015.313 0.75
ONLINE SYSTEM PERMANENT LOCAL 1023.000 347.375 675.625 33.96
ONLINE TOOLS PERMANENT LOCAL 1271.000 123.063 1147.938 9.68
ONLINE UNDOTBS1 UNDO LOCAL 9207.000 2355.313 6851.688 25.58
ONLINE USERS PERMANENT LOCAL 255.000 15.625 239.375 6.13
ONLINE TEMP TEMPORARY LOCAL .000 .000 .000 0.00
11 rows selected.