--表空间使用量
select t.* from (select d.tablespace_name "tablespace_name", space "sum_space(G)", space - nvl(free_space, 0) "used_space(G)", round((1 - nvl(free_space, 0) / space) * 100, 2) "used_rate(%)", free_space "free_space(M)" from (select tablespace_name, round(sum(bytes) / (1024 * 1024 * 1024), 3) space from dba_data_files group by tablespace_name) d, (select tablespace_name, round(sum(bytes) / (1024 * 1024 * 1024), 3) free_space from dba_free_space group by tablespace_name) f where d.tablespace_name = f.tablespace_name(+) union all select d.tablespace_name, space "sum_space(m)", used_space "used_space(m)", round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)", space - used_space "free_space(m)" from (select tablespace_name, round(sum(bytes) / (1024 * 1024 * 1024), 3) space from dba_temp_files group by tablespace_name) d, (select tablespace, round(sum(blocks * 8192) / (1024 * 1024 * 1024), 3) used_space from v$sort_usage group by tablespace) f where d.tablespace_name = f.tablespace(+)) t order by "used_rate(%)" desc; --单个表空间的增长情况,以 小时 为 粒度 select t3.tablespace_id "tablespace_id", t3.name "tablespace_name", t3.full_size "full_size(M)", t3.used_size "used_size(M)", t3.used_size / t3.full_size * 100 "used_ratio(%)", to_char(to_date(t3.db_time, 'mm-dd-yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') "time" from (select distinct t1.tablespace_id, t2.name, t1.tablespace_size * 8192 / 1024 / 1024 full_size, t1.tablespace_usedsize * 8192 / 1024 / 1024 used_size, t1.rtime db_time from dba_hist_tbspc_space_usage t1, v$tablespace t2 where t1.tablespace_id = t2.ts# and t2.name=upper('&tbs_name') order by t1.rtime desc) t3;
--使用绑定变量 --声明绑定变量var tbs_name varchar2(20); --绑定变量赋值exec :tbs_name :='users'; --运用绑定变量select t3.tablespace_id "tablespace_id", t3.name "tablespace_name", t3.full_size "full_size(M)", t3.used_size "used_size(M)", t3.used_size / t3.full_size * 100 "used_ratio(%)", to_char(to_date(t3.db_time, 'mm-dd-yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') "time" from (select distinct t1.tablespace_id, t2.name, t1.tablespace_size * 8192 / 1024 / 1024 full_size, t1.tablespace_usedsize * 8192 / 1024 / 1024 used_size, t1.rtime db_time from dba_hist_tbspc_space_usage t1, v$tablespace t2 where t1.tablespace_id = t2.ts# and t2.name=upper(:tbs_name) order by t1.rtime desc) t3;
--数据文件的增长情况
转载于:https://www.cnblogs.com/iyoume2008/p/6108207.html
