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