Thanks Nigel, you are correct, this method works well with DML but not when you want to drop the table.
Mike Nigel Thomas wrote:
MikeAs Dick explained earlier, this won't work because the constraints are dependent on the table:* FK constraints from this table to others will be dropped when the table is dropped * FK constraints from child tables to this one will prevent the table being dropped unless you use CASCADE CONSTRAINTS (or explicitly drop them first)So you cannot preserve (in the data dictionary) the definition of any constraint dependent on a table while that table is itself being dropped and recreated.The alternative solutionsfor the OP are: * as Dick says, don't bother reorganising columns * as Stefano says, use DBMS_REDEFINITION * or capture the definition of the table and all referencing constraints, indexes etc using DBMS_METADATA, then do the reorganisation, then restore the constraints and indexes Regards Nigel 2010/7/30 Mike Haddon <m.haddon@xxxxxxxxx <mailto:m.haddon@xxxxxxxxx>> set constraints all deferred; <drop and recreate table the way you need to. (make sure data is back)> set constraints all immediate; Mike
