Oracle管理监控之Oracle数据库存储空间监控
1、监控表空间使用率
基表：dba_data_files、dba_free_space
脚本：
select a.tablespace_name,
round((a.maxbytes / 1024 / 1024 / 1024), 2) "sum G",
round((a.bytes / 1024 / 1024 / 1024), 2) "datafile G",
round(((a.bytes - b.bytes) / 1024 / 1024 / 1024), 2) "used G",
round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024 / 1024), 2) "free G",
round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used(%)"
from (select tablespace_name, sum(sumnow) bytes, sum(summax) maxbytes
from (select t1.tablespace_name,
sum(t1.bytes) sumnow,
sum(t1.maxbytes) summax
from dba_data_files t1
where t1.maxbytes <> 0
group by t1.tablespace_name
union all
select t2.tablespace_name,
sum(t2.bytes) sumnow,
sum(t2.bytes) summax
from dba_data_files t2
where t2.maxbytes = 0
group by t2.tablespace_name)
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.maxbytes) desc;
2、创建表空间时指定最大值时查询表空间使用率脚本：
select a.tablespace_name,
round((a.maxbytes / 1024 / 1024 / 1024), 2) "sum G",
round((a.bytes / 1024 / 1024 / 1024), 2) "datafile G",
round(((a.bytes - b.bytes) / 1024 / 1024 / 1024), 2) "used G",
round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024 / 1024), 2) "free G",
round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used(%)"
from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes
from dba_data_files
where maxbytes != 0
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.maxbytes) desc;
3、查看数据文件状态
select t.tablespace_name,
t.file_id,
t.file_name,
t.autoextensible,
ROUND(t.maxbytes / 1024 / 1024 / 1024, 2) || 'G' AS MAXBYTES,
ROUND(t.bytes / 1024 / 1024 / 1024, 2) || 'G' AS BYTES,
ROUND(t.user_bytes / 1024 / 1024 / 1024, 2) || 'G' AS USER_BYTES
from dba_data_files t;
