oracle单个表空间使用情况,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-- 临时表空间使用率

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