Skip to main content

Import Error - deadlock detected while trying to lock object TEST.CLIENT



Have received the below error while doing schema level refresh.

========================================================================
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-04020: deadlock detected while trying to lock object TEST.CLIENT
Failing sql is:
ALTER TABLE "TEST"."TRANSACTION" ADD CONSTRAINT "TRANSACTION_FK" FOREIGN KEY ("LAST_UPDATED_BY_CLIENT_ID") REFERENCES "TEST"."CLIENT" ("CLIENT_ID") ENABLE;

========================================================================

First check for locked objects in the database. using the below queries.

SQL> sqlplus / as sysdba

-------------------------------------------------------------------------

SQL> select * from v$locked_object;

no rows selected

-------------------------------------------------------------------------

SQL> column blocker format a25
SQL> column blockee format a25
SQL> column sid_serial format a10
SQL>
SQL> select
  2  (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
  3  a.sid || ', ' ||
  4  (select serial# from v$session where sid=a.sid) sid_serial,
  5  ' is blocking ',
  6  (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
  7  b.sid || ', ' ||
  8  (select serial# from v$session where sid=b.sid) sid_serial
  9  from v$lock a, v$lock b
 10  where a.block = 1
 11  and b.request > 0
 12  and a.id1 = b.id1
 13  and a.id2 = b.id2;

no rows selected

-------------------------------------------------------------------------

Then try to execute the failed Query connecting to the schema. like below

Connect to that schema & execute.

SQL> ALTER TABLE "TEST"."TRANSACTION" ADD CONSTRAINT "TRANSACTION_FK" FOREIGN KEY ("LAST_UPDATED_BY_CLIENT_ID") REFERENCES "TEST"."CLIENT" ("CLIENT_ID") ENABLE;

Table altered.



Comments

Popular posts from this blog

Registering The Database to RMAN catalog database:

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG