Re: How to reduce fragmentation in one TS?

On my previous reply to this post I forgot to include sql that Pierre-Yves had requested: Here is SQL to map a tablespace.

rem
rem filename: MapTblSpc.sql
rem SQL*Plus script to map the contents of a tablespace. rem
rem 19960820 Mark D. Powell Save well known Oracle script. rem
set pagesize 60
set linesize 80
column owner format A10
column object format A30
set verify off
spool $HOME/MapTblSpc
undefine TS
select 'Free Space' owner, ' ' object, file_id, block_id, blocks from sys.dba_free_space
where tablespace_name = upper('&&TS')
union
select substr(owner,1,20), substr(segment_name,1,32), file_id, block_id,

       blocks
from sys.dba_extents
where tablespace_name = upper('&&TS')
order by 3, 4;
spool off
undefine TS

In article <36B733D1.657CC266_at_us.oracle.com>,   Peter Sharman <psharman_at_us.oracle.com> wrote:
> Pierre-Yves
>
> Yes it will probably cause fragmentation if you are adding mandatory
> columns, maybe not if you're adding nullable ones (depends on the amount
> of data going in). Yes, export/import will remove the fragmentation but
> NO, compress=y is not necessary. This is a common misconception. All
> this will do is put the entire object in one extent (a humongous one
> probably in your case).
>
> HTH.
>
> Pete
>
> Pierre-Yves Kerbiquet wrote:
>
> > Hello,
> >
> > I am adding columns to a large table (billion of rows). I am afraid it
> > creates fragmentation.
> > Can I reduce fragmentation by doing export/import of this table?
> > Do I need to specify COMPRESS=Y when exporting data?
> > Do you have a tip to view fragmentation in one TS?
> >
> > Thank you for your help.
> >
> > PYK
>
> --
>
> Regards
>
> Pete
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Peter Sharman Email: psharman_at_us.oracle.com
> WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> Worldwide Internal Services Education (650)607 0109 (local)
> San Francisco
>
> "Controlling application developers is like herding cats."
> Kevin Loney, ORACLE DBA Handbook
> "Oh no it's not! It's much harder than that!"
> Bruce Pihlamae, long term ORACLE DBA
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 03 1999 - 08:59:18 CST