oracle关于查询空间使用情况的几个sql语句


oracle关于查询空间使用情况的几个sql语句
 
1、查看表空间使用率
 
col Tablespace format a24
col "% Used" for 99.99
select df.tablespace_name "Tablespace",df.bytes/(1024*1024) "Total Size(MB)",
sum(fs.bytes)/(1024*1024) "Free Size(MB)", round(sum(fs.bytes)*100/df.bytes) "% Free",
round((df.bytes-sum(fs.bytes))*100/df.bytes,2) "% Used"
from dba_free_space fs, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df          
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes;
 
2、所有表空间总大小
 
select sum(bytes)/(1024*1024*1024) "Total Size(GB)" from dba_data_files;
 
3、所有表空间剩余大小
 
select sum(bytes)/(1024*1024*1024) "Total Size(GB)" from dba_free_space;
 
4、业务表空间总大小（业务表空间在这里指的是表空间大小>20G的表空间）
 
select sum(df.bytes) / (1024 * 1024 * 1024) total
 from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) df
 where (df.bytes / (1024 * 1024 * 1024)) > 20; 
 
5、业务表空间剩余大小
 
select sum(fs.bytes) total
  from (select tablespace_name, sum(bytes) / (1024 * 1024 * 1024) bytes
          from dba_free_space
         group by tablespace_name) fs
 where fs.tablespace_name in
       (select tablespace_name
          from (select tablespace_name, sum(bytes) bytes
                  from dba_data_files
                 group by tablespace_name) df
         where (df.bytes / (1024 * 1024 * 1024)) > 20);