Re: Question on re-organising Tables

On Wed, 06 Oct 2004 17:06:12 +1000, "Howard J. Rogers" <howardjr_at_dizwell.com> wrote:

>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
>
>

He could do the following :

create table copy_of_foo as (select * from foo);

• could be done "nologging" to save (archive) logs. truncate table foo; -- preserves the indexes in usable state insert into foo (select * from copy_of_foo);

All indexes OK, perfectly reorganised table.

• Kenneth Koenraadt