Re: Create Partitioned Table

Today's a day for cold water !

Don't forget you also have to:

    recreate all indexes
    regenerate all grants
    regenerate all triggers
and

    you hold to be able to hold two copies     of the table and all its indexes
and

    the rebuild may thrash your system to     death as it runs
then

    you may want to rename all the indexes,     constraints, and triggers after you have     finished the rebuild - especially if you     have some hints the name explicit indexes

<Anecdote>
(And I'm not suggesting that Richard is supplying either resource in the following dialogue, but it just suits a lot of Oracle Development environments)

Speaker 1

    Why do you keep throwing cold water on all     my suggestions ?.

Speaker 2

    Cold water is the natural result when hot     air meets thin ice.

</Anecdote>

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

Richard Foote wrote in message ...


>Hi Nilanjan,

>

>IF you're on 9i, there is a very neat way of converting a non partitioned

>table to a partitioned table, via the new online table redefinition

package.


>The beauty with this technique, is that such an alteration can be performed

>with no locking and no disruption (will, minimal anyway) to users on the

>table.

>

>It works in a very similar manner to an index rebuild, but the key

>difference is that unlike an index where the index definition is identical,

>a table can be rebuilt with a totally different definition (eg. drop

>columns, add columns, modify columns, partition, IOT, rename columns,

etc.).


>

>Basically follow these steps:

>

>1) Check that the table can be redefined online with the

>dbms_redefinition.can_redef_table procedure. There are various restrictions

>for a table to be converted, the main one being it must have a PK.

>

>2) Create an empty table that has the desired structure of the table to be

>converted (Note: this can now be a partitioned table)

>

>3) Start the redefinition process with

>dbms_redefinition.start_redef_table(schema, old table, new table, list of

>columns to be converted). This will migrate the table from the old to the

>new table structure.

>

>4) Complete the redefinition process with

>dbms_redefinition.finish_redef_table (schema, old table, new table). This

>completes the process, drops the old table and renames to new one to the

old


>name).

>

>You now have a partitioned table and the users haven't even noticed !!

>

>Obviously, there's a fair bit to all this so a good read up is recommended

>but it is a very nice new feature with 9i (in my tiny little opinion).

>

>Hope it might prove useful one day.

>

>Richard

>"nilanjan" <nilanjan_sarkar_at_hotmail.com> wrote in message

>news:6c8b1f5.0207191044.591ffe7b_at_posting.google.com...

>> Oracle Guru's :

>>

>> Is there a way to CREATE a PARTITIONED table from a non-partitioned

>> one using the CREATE TABLE ..AS SELECT.. syntax ? I couldnt find it in

>> the manuals..

>>

>> ( the few tables in question are about 20 Gigs in size each )

>

>