Re: Tablespace and Segment Frag.

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Fri, 24 Jul 1998 14:44:55 GMT
Message-ID: <01bdb721$6f3526c0$a504fa80@mndnet>

Sanjeev,

I think you are looking for a script, so here we go:

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

Software Administrator <sanjeev_at_pacbell.net> wrote in article <35B6CC2F.4AB5_at_pacbell.net>...
> Hello all,
>
> What is the quick and easy way to find out fragmentation for TB and
> its segments. I have been told to see two views.
>
> For Tablespace
> DBA_FREE_SPACE
>
> For Segments
> DBA_SEGMENTS
>
>
> What columns and values will tell if there is a fragmentation.
>
> Any help will help....
>
> Thanks,
> Sanjeev
>
Received on Fri Jul 24 1998 - 09:44:55 CDT