Home » SQL & PL/SQL » SQL & PL/SQL » Getting an ORA-02264 Error on Table Drop (Oracle 11)
icon9.gif  Getting an ORA-02264 Error on Table Drop [message #433538] Wed, 02 December 2009 20:17 Go to next message
jaypross1
Messages: 3
Registered: December 2009
Junior Member
Hi all,

I have an application running in Oracle. I am not the DBA, and I don't have access to anything but my user schemas.

One function in my application creates and then drops some tables.

Unfortunately, for some of the tables I keep getting the following:
ORA-00604: error occurred at recursive SQL level 1
ORA-02264: name already used by an existing constraint

My assumption is that there is some global configuration (like RMAN, or some type of audit function) that is creating something in the background that I cannot see...

To make this more strange, if I try and use SQL Plus to drop the table, it reports that the object does not exist.

If I use SQL Developer, I can see the table, and drop it without a problem.

Would you have any idea what that might be so I can tell the DBA to turn it off?
Re: Getting an ORA-02264 Error on Table Drop [message #433539 is a reply to message #433538] Wed, 02 December 2009 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>I have an application running in Oracle. I am not the DBA, and I don't have access to anything but my user schemas
Thank Goodness for small favors. This limits the damage you can do within Oracle.

>One function in my application creates and then drops some tables.
Totally unnecessary & an indication you likely developed bad habits on another RDBMS (SQL Server perhaps?).
You REALLY, Really, really should not do so in Oracle!

>Unfortunately, for some of the tables I keep getting the following:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-02264: name already used by an existing constraint
With Oracle you need to believe what the error message say.

>My assumption is that there is some global configuration (like RMAN, or some type of audit function)
>that is creating something in the background that I cannot see...
You assume incorrectly.
Most likely Problem Exists Between Keyboard And Chair.

>To make this more strange, if I try and use SQL Plus to drop the table, it reports that the object does not exist.
If you say so.

>If I use SQL Developer, I can see the table, and drop it without a problem.
Congratulations. So why are you here?

>Would you have any idea what that might be so I can tell the DBA to turn it off?
Do you mean that you DBA can turn you off?

Are you a "software" developer?
Can you debug code you can not see?
I can't debug code I can't see as hard as I may try.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Getting an ORA-02264 Error on Table Drop [message #433540 is a reply to message #433539] Wed, 02 December 2009 20:41 Go to previous messageGo to next message
jaypross1
Messages: 3
Registered: December 2009
Junior Member
I have this same function working on hundreds of instances without ever coming accross this problem. I was merely curious if anybody has come across this same situation before, and if there was some type of RMAN or Audit function that could be causing the problem.
Re: Getting an ORA-02264 Error on Table Drop [message #433541 is a reply to message #433540] Wed, 02 December 2009 20:45 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
does this function exist in a package?

does function or surrounding code do DML (without COMMIT)?

If/when DML is done and no COMMIT or ROLLBACK issued,
then Oracle kind of "locks" objects while pending transaction exists.

As Pogo once said, "We have met the enemy, & they is us."

The logjam might breakup if you TERMINATE every session you have into the DB.
Then again, it may be something else.

[Updated on: Wed, 02 December 2009 20:46]

Report message to a moderator

Re: Getting an ORA-02264 Error on Table Drop [message #433543 is a reply to message #433538] Wed, 02 December 2009 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Getting an ORA-02264 Error on Table Drop
I doubt you got ORA-02264 from DROP TABLE ...
02264, 00000, "name already used by an existing constraint"
// *Cause: The specified constraint name has to be unique.
// *Action: Specify a unique constraint name for the constraint.

perhaps this error occured during CREATE TABLE,but not DROP!
Re: Getting an ORA-02264 Error on Table Drop [message #433566 is a reply to message #433538] Thu, 03 December 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Search for all DDL trigger and post the code. Don't forget to format your post (read OraFAQ Forum Guide).

Regards
Michel

[Updated on: Thu, 03 December 2009 00:50]

Report message to a moderator

Re: Getting an ORA-02264 Error on Table Drop [message #433569 is a reply to message #433538] Thu, 03 December 2009 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jaypross1 wrote on Thu, 03 December 2009 03:17

To make this more strange, if I try and use SQL Plus to drop the table, it reports that the object does not exist.

If I use SQL Developer, I can see the table, and drop it without a problem.

You connect as two different users or at two different instances (use different tnsnames.ora files?)

Like BlackSwan told you, creating and dropping objects at runtime (except for installation code) is a big NO-NO in Oracle. Just don't do that.
Re: Getting an ORA-02264 Error on Table Drop [message #434623 is a reply to message #433569] Thu, 10 December 2009 00:07 Go to previous messageGo to next message
jaypross1
Messages: 3
Registered: December 2009
Junior Member
All,

It turns out that it was related to the recycle bin feature.

Apparently, when you drop a table, Oracle moves it to the recycle bin.

Well that includes the constraints, so it was trying to copy the constraints to the recycle bin. Well the constraints are for columns that are not null, so Oracle just auto generated the constraint names... that was resulting in duplicated names in the recycle bin.

I was able to reproduce over and over and over again... the simple solution was to either turn off the recycle bin, or change my app to run the PURGE RECYCLEBIN; command.

Thanks for your help.

Jay
Re: Getting an ORA-02264 Error on Table Drop [message #434638 is a reply to message #434623] Thu, 10 December 2009 01:17 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was able to reproduce over and over and over again

Too bad you never posted a test case that WE should be able to reproduce it, otherwise I bet you'd have the answer must faster.

Regards
Michel
Previous Topic: Locks on Row and Table in Oracle
Next Topic: Job scheduling problem
Goto Forum:
  


Current Time: Sat Dec 03 14:09:55 CST 2016

Total time taken to generate the page: 0.13883 seconds