在数据库上对 ogg 用户分配权限时，报错：
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg','capture');
BEGIN dbms_goldengate_auth.grant_admin_privilege('ogg','capture'); END;
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "SYS.DBMS_XSTREAM_ADM_INTERNAL", line 2882
ORA-06512: at "SYS.DBMS_GOLDENGATE_AUTH", line 24
ORA-06512: at line 1报错是获取锁超时，所以是锁等待的问题。
先记录授权语句所在会话的信息：
SQL> select sys_context('userenv','sid') as current_sid from dual;
CURRENT_SID
--------------------
1708
SQL> select sid,serial# from v$session where sid=1708;
SID SERIAL#
---------- ----------
1708 1683再次运行上面的授权语句，查看当前的锁等待关系：
SQL> select INST_ID,SID,SERIAL#,MACHINE,PROGRAM,SQL_ID,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_TIME,EVENT from gv$session where BLOCKING_SESSION is not null;
INST_ID SID SERIAL# MACHINE PROGRAM SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION WAIT_TIME EVENT
---------- ---------- ---------- -------------------- ------------------------------ ------------- ----------------- ---------------- ---------- ------------------------------
2 147 53655 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 433 33535 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 575 11765 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 718 19907 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 860 7265 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1001 43797 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1142 19109 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1284 20051 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1427 20445 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1569 17799 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1708 1683 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) b6rpf3hc89b7c 1 2135 0 library cache lock
2 1854 22985 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 1856 58045 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
2 2138 33629 mobiledb2 sqlplus@mobiledb2 (TNS V1-V3) cd7yqpfkpbrd8 2 1993 0 cursor: pin S wait on X
1 13 7401 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 152 42599 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 156 41243 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 296 46259 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 573 28118 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 726 32804 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 1432 62601 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 1433 18969 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 1568 38607 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 1714 30019 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 1861 1193 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 2142 44945 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X
1 2145 23657 mobiledb1 sqlplus@mobiledb1 (TNS V1-V3) cd7yqpfkpbrd8 1 294 0 cursor: pin S wait on X被阻塞的SQL 'cd7yqpfkpbrd8' 是用于监控ADG日志缺口数量的SQL语句，随着时间的增长，越来越多的执行该语句的会话被阻塞。
其中有一条是 libary cache lock 等待，等待锁的会话是2号节点的 (1708,1683)，虽然通过SQL_ID无法查询到对应的SQL_TEXT，但它就是我们上面执行授权语句的会话。
持有锁的会话是1号节点的2135会话，查询其会话信息：
col osuser for a10;
col machine for a20;
col username for a10;
col program for a10;
set lines 200;
set pages 800;
select TADDR,INST_ID,SID,SERIAL#,USERNAME,OSUSER,MACHINE,PROGRAM,PREV_SQL_ID,SQL_ID,EVENT
from gv$session
where inst_id=1 and sid=2135;
INST_ID SID SERIAL# USERNAME OSUSER MACHINE PROGRAM PREV_SQL_ID SQL_ID EVENT
------- ------ ---------- ---------- --------- -------- ---------------- -------- ------------ -----------
1 2135 15 DBSNMP oracle mobiledb1 JDBC Thin client a0qc12302fzfk dawtcrac46cnb SQL*Net message from Client 通过查询PREV_SQL_ID和SQL_ID都是 DBSNMP 的内部SQL。
SQL> select sql_text from v$sql where sql_id='a0qc12302fzfk';
SQL_TEXT
--------------------------------------------------------------------------------
begin dbms_application_info.set_module(:1 , :2 ); end;
SQL> select sql_text from v$sql where sql_id='dawtcrac46cnb';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------
declare l_valid_count number; l_sqlcmd varchar2(256); obs_status varchar2(256); v_db_version VARCHAR2(12); db_version_11 CONSTANT VARCHAR2(10) := '11.1.0.0.0'; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; l_observer_host varchar2(512); begin l_valid_count := -1; select LPAD(version, 10, '0') into v_db_version from v$instance; if(v_db_version >= db_version_11) then select count(1) into l_valid_count from v$database where database_role='PRIMARY' and FS_FAILOVER_STATUS !='DISABLED'; if l_valid_count > 0 then l_valid_count := sys.dbms_drs.get_property_obj(0, 'ObserverHB');
else l_valid_count := -1; end if; end if; if l_valid_count = -1 then obs_status := 'Fast-start failover disabled'; elsif l_valid_count = 0 then select fs_failover_observer_host into l_observer_host from v$database; if l_observer_host is not null then obs_status := 'Observer is running normally on ' || l_observer_host; else obs_status := 'Observer not started.'; end if; elsif l_valid_count > 0 thenDBSNMP 是 OEM 使用的用户，停用 oem agent 后，锁释放，再次授权，瞬间完成。
(oracle) $ /opt/database/em13c/agent/agent_13.2.0.0.0/bin/emctl stop agent