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;