what's the step to reorganize a table by using
CTAS(create table as select)?
let's say we have table parent and child.
create table parent_new as select * from parent.
rename parent to parent_old;
rename parent_new to parent.
but do we need to lock table parent during the CTAS?
if not, what happen if somebody insert some new record to
parent while we copy it over to parent_new?
some record may lost.
Don't do the table reorgs this way, particulary if you are on 8i. It is almost 100% you will forget something - you have to take care of:
FKs from this table,
FKs from other tables referencing this table,
Why not simply use ALTER TABLE MOVE command - you will only have to rebuild (not recreate!) indexes, everything else will be taken care of by the system.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
That was a good point. Yes, I have forgot to think of that Jurij!! Thanx for the correction... But in 8i STD the online rebuild, coalesce are not availabe. So on such cases I personally would resort to export and import. Where I would only have to mark my triggers. ...