Drop goldengate用戶時報ORA-00604 ORA-20782 ORA-06512問題解決

1、問題現(xiàn)象
SQL> drop user goldengate cascade;

Drop goldengate用戶時，報ORA-00604 ORA-20782 ORA-06512錯誤，具體報錯內(nèi)容如下：

drop user goldengate cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot

DROP object used in Oracle GoldenGate replication while trigger is enabled.

Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical

Support if you wish to do so., error stack: ORA-06512: at line 261

ORA-06512: at line 1111

2、原因分析
由于在安裝OGG時，配置并開啟了DDL捕獲功能，而OGG的DDL捕獲，是依賴DDL觸發(fā)器實現(xiàn)的，，DDL處于enabled狀態(tài)，drop goldengate user操作也屬于DDL操作，所以產(chǎn)生ORA-00604 ORA-20782錯誤

3、驗證DDL觸發(fā)器狀態(tài)
SQL> set linesize 999

SQL>select owner,trigger_name,trigger_type,triggering_event,status from dba_triggers where trigger_name like 'GGS%';

OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS

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

SYS GGS_DDL_TRIGGER_BEFORE BEFORE EVENT DDL ENABLED

4、刪除觸發(fā)器
SQL>drop trigger sys.GGS_DDL_TRIGGER_BEFORE

5、再次嘗試刪除用戶
SQL> drop user goldengate cascade;

drop user goldengate cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

再次報錯，但是報錯內(nèi)容發(fā)生了改變

報錯意思為：試圖創(chuàng)建，更改或刪除正在使用的臨時表中的索引

6、找出正在使用臨時表的會話，并killsession
SQL>select 'alter system kill session '''||sid||','||serial#||'''' from v$session where sid in

(select sid from v$lock where id1 in

(select object_id from dba_objects where object_name in (select table_name from dba_tables where owner='GOLDENGATE')));

7、再次刪除用戶
SQL> drop user goldengate cascade;

User dropped.

刪除成功。

本文永久更新鏈接地址：

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com