linux查找表空间使用情况,查看表空间使用情况 。

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Extent Management",

NVL(a.bytes / 1024 / 1024, 0) "Size (M)",

NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used (M)",

NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "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(+) AND

NOT

(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')

UNION ALL

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Extent Management",

NVL(a.bytes / 1024 / 1024, 0) "Size (M)",

NVL(t.bytes, 0) / 1024 / 1024  "Used (M)",

NVL(t.bytes / a.bytes * 100, 0) "Used %"

FROM sys.dba_tablespaces d,

(select tablespace_name, sum(bytes) bytes

from dba_temp_files

group by tablespace_name) a,

(select tablespace_name, sum(bytes_cached) bytes

from v$temp_extent_pool

group by 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';