Printable View
what's the step to reorganize a table by using
CTAS(create table as select)?
let's say we have table parent and child.
we can
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.
give me a hint
That would be inconsistent creation. Instead you could lock and do this, that way you can ensure the consistency. Other wise, once after you do this move, then do the consistency check.
Sam
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:
indexes,
PKs,
UKs,
check constraints,
FKs from this table,
FKs from other tables referencing this table,
triggers,
grants,
snapshot logs,
etc etc...
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.
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. ...
Sam
[Edited by sambavan on 08-17-2001 at 01:59 AM]
This is news to me. Are you sure about that?Quote:
Originally posted by sambavan
[B]But in 8i STD the online rebuild, coalesce are not availabe.
I know STD lacks some of the EE functionality, but I was under the impression the TABLE MOVE, INDEX REBUILD and TS COALESCE are availabe in STD also....