Home » SQL & PL/SQL » SQL & PL/SQL » Truncate table error (ORACLE,10g)
Truncate table error [message #445760] Wed, 03 March 2010 23:32 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Before truncating the table I have disabled the constraints in a schema using the below dynamic query .
select 'ALTER TABLE ' || lower(table_name) || ' DISABLE CONSTRAINT ' || lower(constraint_name) || ';' from user_constraints where constraint_type = 'R'

Then I have executed the 'truncate' statement on all the tables of the schema . But it was giving the below error on one table
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys". When I checked the referntial constraint status it was disabled . But I could execute the "delete" statement successfully on that table . Can any one explain the reason for this and solution also
Re: Truncate table error [message #445761 is a reply to message #445760] Wed, 03 March 2010 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
How can we/I reproduce this situation?
Re: Truncate table error [message #445763 is a reply to message #445760] Thu, 04 March 2010 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You forgot some constraints.

Regards
Michel
Re: Truncate table error [message #445765 is a reply to message #445760] Thu, 04 March 2010 00:25 Go to previous messageGo to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Check any triggers are there for any of the table and pointing to some other tables that you didn't disable the constraints.
Also check any referential tables are there.
Re: Truncate table error [message #445767 is a reply to message #445765] Thu, 04 March 2010 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Truncate != delete.
Normal DML triggers do not fire for truncates. Besides, they could not result in the given error.
Re: Truncate table error [message #445770 is a reply to message #445760] Thu, 04 March 2010 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you've created ALTER TABLE statement with information found in USER_CONSTRAINTS, perhaps there are other users who are referencing this table so ... perhaps you should use ALL_CONSTRAINTS (or even DBA_CONSTRAINTS) instead.
Re: Truncate table error [message #445808 is a reply to message #445770] Thu, 04 March 2010 04:09 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Actually this table is getting referenced by another table which is another another schema
Re: Truncate table error [message #445812 is a reply to message #445760] Thu, 04 March 2010 04:11 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does this mean you've solved your problem?
Re: Truncate table error [message #445856 is a reply to message #445808] Thu, 04 March 2010 06:56 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
YEs that is what they said..
Quote:
As you've created ALTER TABLE statement with information found in USER_CONSTRAINTS, perhaps there are other users who are referencing this table so ... perhaps you should use ALL_CONSTRAINTS (or even DBA_CONSTRAINTS) instead.

Quote:
You forgot some constraints


sriram Smile
Previous Topic: Need help in function
Next Topic: how to use not exists clause in this scenario or do we have any other option
Goto Forum:
  


Current Time: Fri Sep 30 17:28:38 CDT 2016

Total time taken to generate the page: 0.32850 seconds