ORA-04020 SYS.CDC_ALTER_CTABLE_BEFORE

今天在升级9204到9208的过程中遇到了如下问题,记录如下:

SQL> startup
ORACLE instance started.

Total System Global Area  605098840 bytes
Fixed Size                   451416 bytes
Variable Size             268435456 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 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

解决办法:
1.生成pfile
create pfile='/home/oracle/pfile0526' from spfile

2.在pfile中添加如下内容
*._system_trig_enabled=false
*.aq_tm_processes=0
*.job_queue_processes=0

3.使用pfile启动
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/pfile0526
ORACLE instance started.

Total System Global Area  605098840 bytes
Fixed Size                   451416 bytes
Variable Size             268435456 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>

4.编译失效对象
SQL>@/u01/app/oracle/product/9.2.0/db_1/rdbms/admin/utlrp.sql

5.去掉之前添加的三项后重新其他数据库
在pfile文件中将如下三项去掉
*._system_trig_enabled=false
*.aq_tm_processes=0
*.job_queue_processes=0

SQL> shutdown immediate
SQL> startup pfile=/home/oracle/pfile0526
ORACLE instance started.

Total System Global Area  605098840 bytes
Fixed Size                   451416 bytes
Variable Size             268435456 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

6.创建spile
create spfile from pfile='/home/oracle/pfile0526'

7.重新启动数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  605098840 bytes
Fixed Size                   451416 bytes
Variable Size             268435456 bytes
Database Buffers          335544320 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.


-- The End --