oracle查看空闲可用表空间sql语句

jjg6256308

      有时候我们经常要查看可用的表空间还有多少，通常我们市看dba_free_space 这张表，但这张表看起来不怎么直观，所以就写了下面的sql语句，比较直观的查看可用表空间容量：

     

1. select tablespace_name,
   
2.  max_blocks,
   
3.  count_blocks,
   
4.  sum_free_blocks,
   
5.  sum_free_MB,
   
6.  sum_space_MB,
   
7.  round(100*sum_free_blocks/sum_alloc_blocks,2)||'%' pct_free
   
8. from
   
9.  (select tablespace_name,
   
10.   sum(blocks) sum_alloc_blocks,
    
11.   sum(bytes)/1024/1024 sum_space_MB
    
12.  from dba_data_files
    
13.  group by tablespace_name
    
14.  ),
    
15.  (select tablespace_name free_space_tablespace_name,
    
16.   max(blocks) max_blocks,
    
17.   count(blocks) count_blocks,
    
18.   sum(blocks) sum_free_blocks,
    
19.   sum(BYTES)/1024/1024 sum_free_MB
    
20.  from dba_free_space
    
21.  group by tablespace_name
    
22.  )
    
23. where tablespace_name = free_space_tablespace_name