Re: Create Partitioned Table

Hi Jonathon,

Brrrrr, splutter splutter, cough, cough, aaaarrrrrrchhhhooooooooo, sniff ...

Thank you very much. There I was enjoying sunny Canberra and now I'm covered in freezing water !! You could at least have recycled some of the hot water from the hot water bottles you guys use in the UK :)

Yes, all your issues are valid and would generally be performed between steps 3 and 4. Except the renaming bit which would be performed at the very end.

However, the ability to be able to perform this type of activity can be very useful from an availability point of view. Large table, requires constant availability, but really does need to be redefined as partitioned but without that particular column (for example). This can now be performed with potentially no disruption to our users. Yes there is some fiddling about, yes you need the available space aka index rebuilds but it *IS* a very nice option to have available (in my tiny, little, mumbling opinion).

Please, please, please use warmer water next time (with a touch of bourbon :)

Regards

Richard

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1027249781.7369.2.nnrp-10.9e984b29_at_news.demon.co.uk...
> 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 )
> >
> >
>
>
Received on Sun Jul 21 2002 - 07:18:20 CDT