Lets say you have one row in
your table, with ID = 1. Someone decides they need to have a
record with ID = 10, and so they insert using
insert into table (id) values (10).
You now have a ticking timebomb -
the sequence will continue to increment 2, 3 etc, until it
reaches 10 at which time the insert fails with
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C002948) violated
Conversely, a trigger method would
overwrite a value from an explicit ID value using a value specified
in the sequence instead.
Many users experience ORA-00001.
MOSC offers information regarding trace files and ORA-00001 applying to
version 11.5.9 on all platforms.
Here, it is commonly found that
ORA-0001 will be thrown when an item is being imported. Though ORA-00001
does not appear in the log file, neither is ORA-0001 appearing in
MTL_INTERFACES_ERRORS, when the import complete the table is shown as
failing upon enable INCOIN. ORA-0001 is shown in
MTL_INTERFACES_ERRORS as err=1 ie unique constraint violated.
This signifies ORA-00001 because of an orphan record in the
MTL_REVISIONS_INTERFACE table, perhaps because of an import program that has
been run in the past.
ORA-0001 is an issue here because upon
inserting a new record in MTL_SYSTEM_ITEMS_INTERFACE the import inserts
the record in MTL_REVISIONS_INTERFACE before to attempts passing the
record to MTL_ITEM_REVISIONS_B via the MTL_REVISIONS_INTERFACE.
Because SELECT via MTL_REVISIONS_INTERFACE fetch one or more
records, due to orphan records, the INSERT fails, and ORA-0001 is thrown.
MOSC advises that upon truncating MTL_REVISIONS_INTERFACE will
take care of ORA-00001by using the instructions below:
- Create table backup_<tarno> as select * from MTL_REVISIONS_INTERFACE
- Truncate table MTL_REVISIONS_INTERFACE
- Resubmit item for item import
