set pagesize 50
col tablespace_name for a20
col "Tempfile name" for a42
set linesize 300
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2)  "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as "Used_Rate(%)" 
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;

TABLESPACE_NAME      Tempfile name                                total MB    Free MB    Used MB Used_Rate(%)
-------------------- ------------------------------------------ ---------- ---------- ---------- ------------
TEMP                 /oradata/TESTDB/temp01.dbf                        906        882         24         2.65

Elapsed: 00:00:00.03

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle