ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看表空间使用情况

查看表空间使用情况

原创 Linux操作系统 作者:nokilled 时间:2009-01-18 21:15:38 0 删除 编辑
column TABLESPACE_NAME format a20;
SELECT dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %"
FROM
dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL' AND dts.contents like 'TEMPORARY')
UNION ALL
SELECT dts.tablespace_name,
NVL(dtf.bytes / 1024 / 1024, 0) avail,
NVL(t.bytes, 0)/1024/1024 used,
NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024 free,
TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %"
FROM
dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) dtf,
(select tablespace_name, sum(bytes_used) bytes from v$temp_space_header group by tablespace_name) t
WHERE
dts.tablespace_name = dtf.tablespace_name(+)
AND dts.tablespace_name = t.tablespace_name(+)
AND dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY';

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/46907/viewspace-541361/,如需转载,请注明出处,否则将追究法律责任。

上一篇: hang oracle
请登录后发表评论 登录
全部评论

注册时间:2007-12-30

  • 博文量
    108
  • 访问量
    291444