oracle 查询 表空间使用率 sql

前段时间根据业务需要，就琢磨着监控一下表空间使用状态，对百分比超过80的表空间进行及时的扩容，百度上查询了一些例子sql,自己有改动一下，再次分享给大家：

set lines 200
set pages 40
/**  data tablespace **/
select * from (
select a.tablespace_name, round(c.bytes/1024/1024,1) as "Free Size M",a.bytes/1024/1024 as "Size M",
round((a.bytes - c.bytes)/1024/1024,1)  as "Used Size M",round(b.bytes/1024/1024 ,1) as "Max Size M",
cast(((a.bytes - c.bytes)/a.bytes )*100  as int ) as "Use %",cast(((a.bytes - c.bytes)/b.bytes )*100 as int ) as "Max Used%"
from 
(select tablespace_name ,sum(bytes)  as bytes  from dba_data_files group by tablespace_name ) a,
(SELECT TABLESPACE_NAME ,SUM(BYTES) as  bytes  FROM (
 select tablespace_name ,sum(bytes) as bytes from dba_data_files where autoextensible=upper('no')  
group by tablespace_name 
union all 
select tablespace_name ,sum(maxbytes) as bytes from dba_data_files where autoextensible=upper('yes')
group by tablespace_name ) GROUP BY TABLESPACE_NAME  ) b,
(select tablespace_name,sum(bytes) as bytes  from  dba_free_space group by tablespace_name ) c
WHERE a.tablespace_name = b.tablespace_name(+) 
   AND b.tablespace_name = c.tablespace_name(+)
UNION  ALL  /**  temp tablespace **/
select a.tablespace_name, round(c.bytes/1024/1024,1)as "Free Size M",round(a.bytes/1024/1024,1) as "Size M",
round((a.bytes - c.bytes)/1024/1024,1)  as "Used Size M" ,round(b.bytes/1024/1024 ,1)  as "Max Size M",
cast(((a.bytes - c.bytes)/a.bytes )*100  as int) as  "Use %",cast(((a.bytes - c.bytes)/b.bytes )*100 as int ) as "Max Used%"
from 
(select tablespace_name ,sum(bytes)  as bytes  from dba_temp_files group by tablespace_name ) a,
(SELECT TABLESPACE_NAME ,SUM(BYTES) as bytes  FROM (
 select tablespace_name ,sum(bytes) as bytes from dba_temp_files where autoextensible=upper('no')  
group by tablespace_name 
union all 
select tablespace_name ,sum(maxbytes) as bytes from dba_temp_files where autoextensible=upper('yes')
group by tablespace_name ) GROUP BY TABLESPACE_NAME ) b,
(select tablespace_name,sum(free_space) as bytes  from  dba_temp_free_space group by tablespace_name ) c
WHERE a.tablespace_name = b.tablespace_name(+) 
   AND b.tablespace_name = c.tablespace_name(+)
   )  order by 7  desc    
 

　

执行结果输出参考（测试机）

SQL> set lines 200
SQL> set pages 40
SQL> /**  data tablespace **/
SQL> select * from (
  2  select a.tablespace_name, round(c.bytes/1024/1024,1) as "Free Size M",a.bytes/1024/1024 as "Size M",
  3  round((a.bytes - c.bytes)/1024/1024,1)  as "Used Size M",round(b.bytes/1024/1024 ,1) as "Max Size M",
  4  cast(((a.bytes - c.bytes)/a.bytes )*100  as int ) as "Use %",cast(((a.bytes - c.bytes)/b.bytes )*100 as int ) as "Max Used%"
  5  from 
  6  (select tablespace_name ,sum(bytes)  as bytes  from dba_data_files group by tablespace_name ) a,
  7  (SELECT TABLESPACE_NAME ,SUM(BYTES) as  bytes  FROM (
  8   select tablespace_name ,sum(bytes) as bytes from dba_data_files where autoextensible=upper('no')  
  9  group by tablespace_name 
 10  union all 
 11  select tablespace_name ,sum(maxbytes) as bytes from dba_data_files where autoextensible=upper('yes')
 12  group by tablespace_name ) GROUP BY TABLESPACE_NAME  ) b,
 13  (select tablespace_name,sum(bytes) as bytes  from  dba_free_space group by tablespace_name ) c
 14  WHERE a.tablespace_name = b.tablespace_name(+) 
 15     AND b.tablespace_name = c.tablespace_name(+)
 16  UNION  ALL  /**  temp tablespace **/
 17  select a.tablespace_name, round(c.bytes/1024/1024,1)as "Free Size M",round(a.bytes/1024/1024,1) as "Size M",
 18  round((a.bytes - c.bytes)/1024/1024,1)  as "Used Size M" ,round(b.bytes/1024/1024 ,1)  as "Max Size M",
 19  cast(((a.bytes - c.bytes)/a.bytes )*100  as int) as  "Use %",cast(((a.bytes - c.bytes)/b.bytes )*100 as int ) as "Max Used%"
 20  from 
 21  (select tablespace_name ,sum(bytes)  as bytes  from dba_temp_files group by tablespace_name ) a,
 22  (SELECT TABLESPACE_NAME ,SUM(BYTES) as bytes  FROM (
 23   select tablespace_name ,sum(bytes) as bytes from dba_temp_files where autoextensible=upper('no')  
 24  group by tablespace_name 
 25  union all 
 26  select tablespace_name ,sum(maxbytes) as bytes from dba_temp_files where autoextensible=upper('yes')
 27  group by tablespace_name ) GROUP BY TABLESPACE_NAME ) b,
 28  (select tablespace_name,sum(free_space) as bytes  from  dba_temp_free_space group by tablespace_name ) c
 29  WHERE a.tablespace_name = b.tablespace_name(+) 
 30     AND b.tablespace_name = c.tablespace_name(+)
 31     )  order by 7  desc
 32  /

TABLESPACE_NAME                Free Size M     Size M Used Size M Max Size M      Use %  Max Used%
------------------------------ ----------- ---------- ----------- ---------- ---------- ----------
T_DATA                                 396       2000        1604       2000         80         80
SYSTEM                                  .8        740       739.3      32768        100          2
SYSAUX                                26.9        530       503.1      32768         95          2
USERS                                  3.7         60        56.3      32768         94          0
TEMP                                    27         29           2      32768          7          0
UNDOTBS1                              67.3         75         7.8      32768         10          0

6 rows selected.

SQL> 

　

列名参数说明：表空间名称 ，空闲容量，表空间数据文件当前大小，表空间使用大小，表空间最大可扩展大小，使用百分比，最大可扩展使用百分比。