Xref: alice comp.databases.oracle.server:39673
Path: alice!news-feed.fnsi.net!netnews.com!newspeer1.nac.net!news.maxwell.syr.edu!nntp2.dejanews.com!nnrp1.dejanews.com!not-for-mail
From: mark.powell@eds.com
Newsgroups: comp.databases.oracle.server
Subject: Re: How to reduce fragmentation in one TS?
Date: Wed, 03 Feb 1999 14:59:18 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 82
Message-ID: <799o7v$f72$1@nnrp1.dejanews.com>
References: <7978ih$8o4$1@minus.oleane.net> <36B733D1.657CC266@us.oracle.com>
X-Article-Creation-Date: Wed Feb 03 14:59:18 1999 GMT
X-Http-User-Agent: Mozilla/2.0 (compatible; MSIE 3.02; Windows 95)
X-Http-Proxy: 1.0 x11.dejanews.com:80 (Squid/1.1.22) for client 199.228.142.8

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@us.oracle.com>,
  Peter Sharman <psharman@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@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