March 11, 2010
Sometimes I receive seemingly interesting emails showing Oracle errors – leaving me to ponder… certainly, that can’t cause an error, can it? Here is one that I received a year ago (paraphrased):
The commercially developed application that we are using displayed an error message identifying a SELECT statement as the source of an ORA-00001 error. What is the source of the Oracle constraint error? The error message displayed by the application is as follows:select account_period from PROJECT_SUMMARY where project_id = :m_saProjSumProjectID[nProjSumIndex] and id =:m_saProjSumSavedID[nProjSumIndex] into :nACCOUNT_PERIOD ORA-00001: unique constraint (TESTUSER.SYS_C006354) violated This transaction has resulted in violating an Oracle defined constraint. Constraints are enforced by the database manager. This transaction has been rolled back.
My first thought at the sight of this error was that the commercially developed application was actually displaying one of the SQL statements that was executed after the SQL statement which triggered the primary key violation. It is easy to let a runtime error slide through for a period of time before the program notices that an error happened – maybe it is just a sign of sloppy programming (I hope not, because this has happened in some of my custom-developed programs too).
How would we start to troubleshoot this error message? The “SYS_C” portion of the constraint name indicates that the constraint is most likely a system generated constraint name, probably intended to enforce a uniqueness requirement for a primary key column. Exporting the data from the database using Datapump export (or the legacy exp utility) and importing the data into a new database could cause the number following “SYS_C” to change, and it is likely that constraint SYS_C006354 in my database (that is used by the same application) is very different from that of the person who posed the question to me.
Let’s see if we are able to find the answer by working the problem in a circular fashion. For example, let’s find the name of the index that is used to enforce the primary key constraint on of one the application’s tables:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'SYS%' AND TABLE_NAME='INVENTORY_TRANS'; INDEX_NAME ----------- SYS_C005168
Now that we know that the index is named SYS_C005168, we could do something like this:
SELECT DC.OWNER, DC.CONSTRAINT_NAME, DC.CONSTRAINT_TYPE, DC.TABLE_NAME, DC.STATUS, SUBSTR(DCC.COLUMN_NAME,1,30) COLUMN_NAME FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS DCC WHERE DC.CONSTRAINT_NAME='SYS_C005168' AND DC.OWNER='TESTUSER' AND DC.OWNER=DCC.OWNER AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME ORDER BY DCC.POSITION; OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS COLUMN_NAME ------ --------------- --------------- --------------- ------- -------------- SYSADM SYS_C005168 P INVENTORY_TRANS ENABLED TRANSACTION_ID
The above output shows that the primary key constraint SYS_C005168 enforces the uniqueness of the primary key (TRANSACTION_ID) column in the table INVENTORY_TRANS. We just demonstrated that we now know what we already mostly knew.
In the case of the person who sent the email to me, the table name was not known. So, we could take the last of the above SQL statements and substitute SYS_C006354 in place of SYS_C005168 to find the table name and primary key column that was violated. If the SQL statement failed to return usable information the next step might be to enable a 10046 trace at level 4 for one of the affected sessions, and try to reproduce the problem. A 10046 trace will list the sequence of events that led up to the error message appearing in the client application.