查看表空间剩余空间的详细信息

- PL/SQL script. to determine how much contiguous  

free space is available for each tablespace in the database.  Oracle will  

acquire space by searching first for exact fit, then next best fit, and lastly  

coalesce if possible.  

create table SPACE_TEMP ( 

 TABLESPACE_NAME        CHAR(30), 

 CONTIGUOUS_BYTES       NUMBER) 

/ 

 

declare 

  cursor query is select * 

                  from dba_free_space 

                  order by tablespace_name, block_id; 

  this_row        query%rowtype; 

  previous_row    query%rowtype; 

  total           number; 

 

begin 

  open query; 

  fetch query into this_row; 

  previous_row := this_row; 

  total := previous_row.bytes; 

  loop 

     fetch query into this_row; 

     exit when query%notfound; 

     if this_row.block_id = previous_row.block_id + previous_row.blocks then 

        total := total + previous_row.bytes; 

        insert into SPACE_TEMP (tablespace_name) 

                  values (previous_row.tablespace_name); 

     else 

        insert into SPACE_TEMP values (previous_row.tablespace_name, 

                                 total); 

        total := this_row.bytes; 

     end if; 

     previous_row := this_row; 

  end loop; 

  insert into SPACE_TEMP values (previous_row.tablespace_name, 

                           total); 

end;  

/ 

 

set pagesize 60 

set newpage 0 

set echo off 

ttitle center 'Contiguous Extents Report' - 

       skip 1 center new_today skip 3 

break on "TABLESPACE NAME" skip page duplicate 

spool contig_free_space.lis 

rem 

column "CONTIGUOUS BYTES"       format 999,999,999 

column "COUNT"                  format 999 

column "TOTAL BYTES"            format 999,999,999 

column "TODAY"   noprint new_value new_today format a1 

rem 

select TABLESPACE_NAME  "TABLESPACE NAME", 

       CONTIGUOUS_BYTES "CONTIGUOUS BYTES", 

       count(*)         "COUNT", 

       CONTIGUOUS_BYTES*count(*) "TOTAL BYTES", 

       to_char(sysdate,'FMMonth DD, YYYY') "TODAY" 

from SPACE_TEMP 

where CONTIGUOUS_BYTES is not null 

group by TABLESPACE_NAME, CONTIGUOUS_BYTES 

order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc 

/ 

spool off 

 

drop table SPACE_TEMP 

/ 

 

exit 

/ 

以上脚本会输出数据库中所有表空间剩余空间的详细信息，

执行完之后输入如下，如第一行的意思代表temp表空间里面3.5M的Extent有1个

A sample output: 

                            Contiguous Extents Report 

                                   May 28, 2001

 

 

TABLESPACE NAME                CONTIGUOUS BYTES COUNT  TOTAL BYTES 

------------------------------ ---------------- ----- ------------ 

TEMP                                  3,584,000     1    3,584,000 

TEMP                                  1,781,760     1    1,781,760 

TEMP                                  1,740,800     1    1,740,800 

TEMP                                  1,536,000     1    1,536,000 

TEMP                                  1,126,400     2    2,252,800 

TEMP                                    716,800     1      716,800 

TEMP                                    512,000     1      512,000 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22531473/viewspace-751844/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22531473/viewspace-751844/