SELECT d.tablespace_name "Name" ,
NVL(a.bytes / 1024 / 1024, 0) "Size(MB)",
NVL(t.bytes, 0) / 1024 / 1024 "Used(MB)",
NVL(t.bytes / a.bytes * 100, 0) "Used(%)",
NVL(a.bytes - NVL(t.bytes, 0), 0)/1024/1024 "Free(MB)",
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name ,
sum((ss.used_blocks*ts.blocksize)) bytes
from gv$sort_segment ss,
sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND
d.contents like 'TEMPORARY';
NVL(a.bytes / 1024 / 1024, 0) "Size(MB)",
NVL(t.bytes, 0) / 1024 / 1024 "Used(MB)",
NVL(t.bytes / a.bytes * 100, 0) "Used(%)",
NVL(a.bytes - NVL(t.bytes, 0), 0)/1024/1024 "Free(MB)",
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name ,
sum((ss.used_blocks*ts.blocksize)) bytes
from gv$sort_segment ss,
sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND
d.contents like 'TEMPORARY';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26552866/viewspace-721852/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26552866/viewspace-721852/