Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, pauline.

Asked: January 19, 2002 - 8:59 pm UTC

Last updated: November 12, 2005 - 10:42 am UTC

Version: 8.1.7.2

Viewed 1000+ times

You Asked

Tom,
As I know that compress=y in the export will consolidate table's extents into first extents. So you suggest that always use compress=N.

I have the impression that if use locally managered tablespace, the table being created in that tablespace will always use the initial extent from that tablespace even we specify some different initial extent in the table creation statement.

I am just thinking about this question: If use compress=y in export
for the table which was created in locally managered tablspace, after
import ,the table will get the consolidated initial extent or still
use the initial extent size of that locally managered tablespace ?

Please clarify it.
Thanks.

Pauline

and Tom said...

With an LMT -- ever extent will either be:

o uniformly sized (my preference)
o "auto" sized by the system using some undocumented method for sizing.

If you use a uniform size of 1m and you do an export with compress=y and the initial extent requested was 100m, we will allocate 100 1m extents to satisfy your "initial" request. We will take the INITIAL, NEXT and MINEXTENTS -- decide what they would be in a DMT and preallocate at least that much space in an LMT for you.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

a reader, January 20, 2002 - 11:51 am UTC

Tom,
So if in the case of uniformly sized locally managed tablespace, if tablespace is uniform size of 1m and the table's extents reached to 100M,at this moment, we do export with compress=y or take the default from compress,
oracle will allocate 100 1m extents to satisfy "initial" request--100M, right?
Please confirm it. Thanks.

Tom Kyte
January 20, 2002 - 11:51 am UTC

yes. correct.

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

dost, November 11, 2005 - 3:17 pm UTC


I am on the way to export a schema ,drop Dictonary manage tablespace then creation of LMT and the import back

Do you think following command is ok?
CREATE TABLESPACE FPADATA DATAFILE '/REP/data03/fpadata01.dbf' size 2000m EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
after that I add 94 files of 2Gb size

Thanks!

Tom Kyte
November 12, 2005 - 10:41 am UTC

I would not use export/import for this.

Just use

ALTER TABLE T move TABLESPACE lmt storage (initial 1k next 1k pctincrease 0 );
ALTER INDEX I rebuild tablespace lmt storage (initial 1k next 1k pctincrease 0 );


much less risky - faster - can be done without logging if you like and won't generate any undo.

(180gb in a single tablespace, I'd be tempted to - during this move/reorg - segregate that out into something more manageable - but that is up to you)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.