表空间查询

/*now后缀的是当前的状态，max后缀的是自动扩展后所能达到的状态。*/
SELECT tablespace_name,
       --max_m,
       --count_blocks free_blk_cnt,
       sum_m - sum_free_m sum_used_m,
       sum_free_m sum_free_m_now,
       (sum_free_m + decode(sum_m_max, 0, sum_m, sum_m_max) - sum_m) sum_free_max,
       sum_m sum_m_now,
       decode(sum_m_max, 0, sum_m, sum_m_max) AS sum_m_max,
       to_char(100 * sum_free_m / sum_m, '99999.99') || '%' AS pct_free_now,
       to_char(100 *
               (sum_free_m + decode(sum_m_max, 0, sum_m, sum_m_max) - sum_m) /
               decode(sum_m_max, 0, sum_m, sum_m_max),
               '99999.99') || '%' AS pct_free_max
  FROM (SELECT tablespace_name,
               sum(bytes) / 1024 / 1024 AS sum_m,
               sum(decode(autoextensible,
                          'YES',
                          decode(sign(bytes - maxbytes), '1', bytes, maxbytes),
                          bytes)) / 1024 / 1024 AS sum_m_max
          FROM dba_data_files
         GROUP BY tablespace_name),
       (SELECT tablespace_name AS fs_ts_name,
               --max(bytes) / 1024 / 1024 AS max_m,
               --count(blocks) AS count_blocks,
               sum(bytes / 1024 / 1024) AS sum_free_m
          FROM dba_free_space
         GROUP BY tablespace_name)
 WHERE tablespace_name = fs_ts_name(+)
-- AND tablespace_name not like  'APPS%'
 ORDER BY 8;

--表空间使用情况的查询sql。

SELECT   a.tablespace_name           ,  
         SUM(a.tots   /1048576)        Tot_Size,  
         SUM(a.sumb   /1048576)        Tot_Free,  
         SUM(a.sumb)  *100/SUM(a.tots) Percent_Free,  
         SUM(a.largest/1024)           Max_Free,  
         SUM(a.chunks)                 Chunks_Free  
FROM     ( SELECT  tablespace_name,  
                  0          tots          ,  
                  SUM(bytes) sumb          ,  
                  MAX(bytes) largest       ,  
                  COUNT(*)   chunks  
         FROM     dba_free_space a  
         GROUP BY tablespace_name  
           
         UNION  
           
         SELECT   tablespace_name,  
                  SUM(bytes) tots,  
                  0,0,0  
         FROM     dba_data_files  
         GROUP BY tablespace_name  
         )  
         a  
GROUP BY a.tablespace_name  
ORDER BY pct_free;  

Script 1: Tablespace information
SET linesize 150  
column tablespace_name format a20 heading 'Tablespace'  
column sumb format 999,999,999  
column extents format 9999  
column bytes format 999,999,999,999  
column largest format 999,999,999,999  
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'  
column Tot_Free format 999,999,999 heading 'Total Free(MB)'  
column Pct_Free format 999.99 heading '% Free'  
column Chunks_Free format 9999 heading 'No Of Ext.'  
column Max_Free format 999,999,999 heading 'Max Free(Kb)'  
SET echo OFF  
PROMPT FREE SPACE AVAILABLE IN TABLESPACES  
SELECT   a.tablespace_name           ,  
         SUM(a.tots   /1048576)        Tot_Size,  
         SUM(a.sumb   /1048576)        Tot_Free,  
         SUM(a.sumb)  *100/SUM(a.tots) Percent_Free,  
         SUM(a.largest/1024)           Max_Free,  
         SUM(a.chunks)                 Chunks_Free  
FROM     ( SELECT  tablespace_name,  
                  0          tots          ,  
                  SUM(bytes) sumb          ,  
                  MAX(bytes) largest       ,  
                  COUNT(*)   chunks  
         FROM     dba_free_space a  
         GROUP BY tablespace_name  
           
         UNION  
           
         SELECT   tablespace_name,  
                  SUM(bytes) tots,  
                  0,0,0  
         FROM     dba_data_files  
         GROUP BY tablespace_name  
         )  
         a  
GROUP BY a.tablespace_name  
ORDER BY pct_free;  

Output like:
                         Total
Tablespace            Size(Mb) Total Free(MB)  % Free Max Free(Kb) No Of Ext.
-------------------- --------- -------------- ------- ------------ ----------
SYSTEM                     790              3     .38        3,008          2
SYSAUX                     752             52    6.86       32,768        132
USERS                        5              1   11.25          576          1
MGMT_ECM_DEPOT_TS          100             43   43.25       43,968          2
MGMT_TABLESPACE         13,940          8,388   60.17      155,200       1594
UNDOTBS1                   605            491   81.07      311,360         44
PATROL                       1              1   93.75          960          1

Script 2: Tablespaces With Less Than 10% Free Space
set pagesize 300  
set linesize 100  
column tablespace_name format a15 heading 'Tablespace'  
column sumb format 999,999,999  
column extents format 9999  
column bytes format 999,999,999,999  
column largest format 999,999,999,999  
column Tot_Size format 999,999 Heading 'Total Size(Mb)'  
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'  
column Pct_Free format 999.99 heading '% Free'  
column Max_Free format 999,999,999 heading 'Max Free(Kb)'  
column Min_Add format 999,999,999 heading 'Min space add (MB)'  
  
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2  
set echo off  
  
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,  
sum(a.sumb/1024) Tot_Free,  
sum(a.sumb)*100/sum(a.tots) Pct_Free,  
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add  
from  
(  
select tablespace_name,0 tots,sum(bytes) sumb  
from dba_free_space a  
group by tablespace_name  
union  
select tablespace_name,sum(bytes) tots,0 from  
dba_data_files  
group by tablespace_name) a  
group by a.tablespace_name  
having sum(a.sumb)*100/sum(a.tots) < 10  
order by pct_free;