Re: Create Partitioned Table

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 )
Received on Sun Jul 21 2002 - 02:55:16 CDT