ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 史上表空间查询最好用的脚本

史上表空间查询最好用的脚本

原创 Linux操作系统 作者:willkk88 时间:2011-07-06 20:42:43 0 删除 编辑
set linesize 140 pagesize 10000
col "Status"   for a10
col "Name"     for a25
col "Type"     for a10
col "Extent"   for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %"   for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990') "Size (M)", 
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'999,999,999') "Used (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(+) 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", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,999') "Size (M)", 
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999,999,999') "Used (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "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'
ORDER BY 7 desc;

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

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    15
  • 访问量
    26789