Re: Extent Analysis

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: 8 Apr 1998 22:10:39 GMT
Message-ID: <01bd634b$3ccff2c0$a504fa80@mndnet>

        

HTrieu <htrieu_at_aol.com> wrote in article <1998033010250901.FAA01588_at_ladder01.news.aol.com>...
>
> I would appreciate any assistance in a simple sql script to analyze the
> freespace in an extent. I have a 4GB table in a 78GB database and I am
> trying to size growth of this tablespace. Please excuse my
"inexperience"
> inheritance aspect of the job.
>

Here is a script that you may want to try on UNIX.

set doc off pause off   

/************************************************************************
   *    DESCRIPTION : This script generates tablespace usage for a        
*
   *                  TABLESPACE name entered on the command line.        
*
   *                  WHAT YOU NEED IS THE CONTIGUOUS FREESPACE           

*   

************************************************************************/
 

set termout off pause off feedback off verify off heading on set linesize 80 newpage 0 pagesize 58 space 1  

column today        new_value today   noprint
column time         new_value time    noprint
 
select   to_char(sysdate, 'DD-MON-YYYY') today,
         to_char(sysdate, 'HH:MI:SS AM') time

  from dual;  

spool $HOME/rep/analyze_tablespace_usage_&1..lst  

column file_id          format 9990             heading 'FILE|ID '
column block_id         format 99990            heading 'BLOCK|ID '
column blocks           format 99990            heading 'BLOCKS'
column bytes            format 999,999,999      heading 'BYTES'
 

ttitle today center '&1 TABLESPACE FRAGMENTATION' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2

select   'freespace' owner, '       ' object,
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_free_space
 where tablespace_name = upper('&1')
union
select substr(owner, 1, 15),

         substr(segment_name, 1, 31),
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_extents
 where tablespace_name = upper('&1')
order by 3, 4
/  

column segment_name format a30
column tablespace_name format a15  

ttitle today center '&1 TABLESPACE USAGE' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2
select segment_name, tablespace_name, sum(bytes) bytes   from sys.dba_extents
 where tablespace_name = '&1'
 group by segment_name, tablespace_name /  

set feedback off  

column space                            heading '   SPACE'
column name                             heading '         INDEX NAME'

column btree_space format 999,999,999 heading 'TOTAL SPACE' column used_space format 999,999,999 heading 'USED SPACE'  

ttitle today center 'USED AND FREE &1 TABLESPACE USAGE' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_tablespace_usage.sql skip 2
select 'Free Space ' space, sum(bytes ) bytes   from sys.dba_free_space
 where tablespace_name = upper('&1')
union
select 'Used Space ' space, sum(bytes ) bytes   from sys.dba_extents
 where tablespace_name = upper('&1')
/  

prompt
prompt
prompt. ********** END OF REPORT *********  

spool off  

exit

suresh.bhat_at_mitchell-energy.com Received on Wed Apr 08 1998 - 17:10:39 CDT