Re: Detect Fragmentation of a tablespace

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Fri, 24 Jul 1998 14:51:21 GMT
Message-ID: <01bdb722$459362e0$a504fa80@mndnet>

Have you though about setting pct_increase to 1 for all the tablespaces except Oracle created ones such as RBS, SYSTEM, TOOLS etc. If you do this then SMON will COALESCE contiguous fragmented disk space every couple of munutes or so.

To get a real picture of the fragmentation here is a script:

set pause off termout off verify off wrap on set newpage 0 pagesize 58 linesize 80

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
/

column object           format a26              heading 'OBJECT'
column frag_index       format 990.99
column file_id          format 9990             heading 'FILE|ID '
column block_id         format 999990           heading 'BLOCK|ID '
column blocks           format 999990           heading 'BLOCKS'
column bytes            format 9,999,999,999    heading 'BYTES'
 

spool x.x

ttitle today center 'EFM_HIST TABLESPACE FRAGMENTATION' -

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

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

  from sys.dba_free_space
 where tablespace_name = 'EFM_HIST'
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 = 'EFM_HIST'
order by 3, 4
/
spool off
exit

Good luck !!!

suresh.bhat_at_mitchell-energy.com  

poohland_at_hotmail.com wrote in article <6p7vis$9sq$1_at_nnrp1.dejanews.com>...
> Hi!
>
> Is there any script that can help me to detect the fragmentation of a
> tablespace in order to decide whether I should rebuild it or not?
>
> Thank you very much in advance
>
> Winnie Liu
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Fri Jul 24 1998 - 09:51:21 CDT