Re: Question on re-organising Tables

From: Howard J. Rogers <howardjr_at_dizwell.com>
Date: Wed, 06 Oct 2004 17:06:12 +1000
Message-ID: <4164eb99$0$20581$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

> John Wood wrote:

>> We have Oracle 9i on Win2K environment. We have created some very big
>> tables in a user tablespace. As now we found the tables are not used
>> much.
>> It contains much less data. I would like to rebuild the table using
>> Exp/Imp. But when I export the tables, the export file would contain a
>> statement to create the table with big initial extent.
>>
>> If I export the tables with COMPRESS=N, then I think I can prevent the
>> tables from creating with large initial extent. I have enabled the Local
>> Managed Tablespace. The question is: Is it okay to do so as the Local
>> Managed Tablespace would allocate 64K at a time ? The tables would
>> probably
>> involve about 200M to 500M of data. Will there be any performance issue
>> ?
>>
>> Thanks.
>>
>> JW.
>>
>>

>
> Not withstanding HJR's advice, why don't you just 'alter table move ...' ?

Good suggestion, btw. And it's a bit quibbling of me to mention that there's only one one possible downer with it: moving a table requires potentially twice the amount of Oracle-allocated disk space as the single table uses. Whereas it would be possible to export to some disk that is not otherwise used by Oracle.

Oh, and OK... a second downer: moving a table causes a hell of a lot of buffer cache activity. Export doesn't, in direct path mode anyway.

Oh, and a third possible: moving a table requires that you *remember* to rebuild all your indexes. Importing one does it for you, by default.

Dull it may be, but export still has its uses!!

:-)
HJR

> 
> HTH
> 
> Holger

Received on Wed Oct 06 2004 - 02:06:12 CDT