oracle 表空间使用情况统计

亲们，工作的同时可不要忘了学习哦...

下面是数据库表空间使用情况统计：

select t1.tablespace_name,t1.total_m,(t1.total_m-nvl(t2.free_m,0)) used_m,round((t1.total_m-nvl(t2.free_m,0))/t1.total_m*100,2)||'%' pct_used from
(select tablespace_name,sum(bytes)/1024/1024 total_m from dba_data_files
group by tablespace_name) t1,
(select tablespace_name,sum(bytes)/1024/1024 free_m from dba_free_space
group by tablespace_name) t2
where t1.tablespace_name=t2.tablespace_name(+)--数据文件使用率
union all
select t2.tablespace_name,t2.total_m,t1.used_m,round(t1.used_m/t2.total_m *100,2)||'%' used_pct from
(select tablespace_name,sum(bytes_used)/1024/1024 used_m from v$temp_space_header
group by tablespace_name) t1,
(select tablespace_name,sum(bytes)/1024/1024 total_m from dba_temp_files
group by tablespace_name) t2
where t1.tablespace_name=t2.tablespace_name-- 临时表空间使用率

 

要定时的查看表空间的使用情况，除非数据文件自动扩展，否则，会报错的，尤其是生产库