Drop table does not work even after disabling constraints

[suhasd75]

Hi,

I have a table whose Primary Key column is used to define the Foreign Key of seven other tables.
I now want to drop the parent table: Disabled the seven FKs and tried, didnt work; it gave ora-02449 error.
The following query was used to determine FKs:
select parent.table_name "P", child.table_name "C"
from user_constraints parent, user_constraints child
where child.constraint_type='R'
and parent.constraint_name=child.r_constraint_name
and parent.owner = child.owner
and parent.table_name = 'TABLE_NAME'
order by parent.table_name, child.table_name;

I also imported the datapump file into a sql file and verified; there are only 7 FK constraints on the above parent table.

Also, it is to be mentioned that some tables have a composite Primary Key defined with the column name same as the above table's primary key column name.
Would this have anything to do with the issue at hand?
Please note that the above composite PK tables do not have any FK on the offending table.

Please help!

Regards,
Suhas

[ales]

Disabling FKs does not help. You have to drop them.

$ oerr ora 02449
02449, 00000, "unique/primary keys in table referenced by foreign keys"
// *Cause: An attempt was made to drop a table with unique or
// primary keys referenced by foreign keys in another table.
// *Action: Before performing the above operations the table, DROP the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


And the manual says:

If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:

DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;

[suhasd75]

Yes Ales,

You are right. After I got nowhere with disabling constraints, I just dropped them and it worked fine.
I would rather have the disable work than drop.

Actually, I was doing a kind of reorg, making an existing table to a partitioned table using the Data Pump (export/import) method: FYI

Thanks for the kind reply

Regards,
Suhas

[PAVB]

I just dropped them and it worked fine.

Please do tell you have the DDL needed to recreate these FKs.

If the whole process is done during a maintenance window you may want to recreate FKs with NOVALIDATE option -just to do it faster.

[suhasd75]

Please do tell you have the DDL needed to recreate these FKs.

If the whole process is done during a maintenance window you may want to recreate FKs with NOVALIDATE option -just to do it faster.

Absolutely, PAVB.

I ensured that I had all relevant scripts and also a logical backup (in production, I would do both physical and logical backup).

And thanks for the NOVALIDATE tip; I would have done the same during the actual process.

Thanks again everyone, for the quick reply with resolutions and tips!

Suhas