Deadlock on SYS.CDC_ALTER_CTABLE_BEFORE

Ran into this problem on one of our old Oracle 9.2 test databases.

Running exp had failed due to DBMS_EXPORT_EXTENSIONS being invalid.

Upon shutting down the database cleanly using a shutdown immediate. The database would not start due to the following error:

SQL> startup
ORACLE instance started.

Total System Global Area 185684368 bytes
Fixed Size 455056 bytes
Variable Size 150994944 bytes
Database Buffers 33554432 bytes
Redo Buffers 679936 bytes
Database mounted.
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object
SYS.CDC_ALTER_CTABLE_BEFORE



To get the database open I had to shut the database down and change the following parameters in the pfile:


_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0


SYS.CDC_ALTER_CTABLE_BEFORE is a system trigger and setting the hidden parameter _system_trig_enabled enabled the database to start without firing any triggers.

A startup restrict then allowed me to start up the database in the knowledge that other applications could not access the database while I diagnosed further.

The following SQL

select owner, object_type, count(*)
from dba_objects
where status = 'INVALID'
group by owner, object_type;

Revealed thousands of SYS objects to be invalidated. To compile them I ran the script utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql

This revealed that 82 SYS objects were still invalid including DBMS_EXPORT_EXTENSIONS.

There is obviously something wrong with the data dictionary so lets recreate it:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

Shut down the database
remove the parameters we changed
start the database up.