adop phase=cleanup was taking a lot of time on package DBMS_SYS_SQL and Library cache pin event
SQL> SELECT s.sid,
s.username,
s.program,
s.module from v$session s where module like '%AD_ZD%';
SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MODULE
----------------------------------------------------------------
214 SYSTEM
sqlplus@ip-172-31-46-159.ec2.internal (TNS V1-V3
AD_ZD
SQL> select event from v$session where sid=214;
EVENT
----------------------------------------------------------------
library cache pin
SQL> select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, ' 7 DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PR 8 OFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc;
OBJECT_TYPE
---------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME
-------------- ------------------- ---------- ---------- ------------------------------
PACKAGE
DBMS_SYS_SQL
2 0 214 6036 SYSTEM
PACKAGE
DBMS_SYS_SQL
0 3 214 6036 SYSTEM
OBJECT_TYPE
---------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME
-------------- ------------------- ---------- ---------- ------------------------------
Run the select below and verify 0 rows are returned:
SQL> select count(1)
from dba_tab_privs
where table_name='DBMS_SYS_SQL'
and privilege='EXECUTE'
and grantee='APPS';
COUNT(1)
----------
1
SQL> exec sys.ad_grants.cleanup;
PL/SQL procedure successfully completed.
Run the select below again and verify 0 rows are returned:
SQL> select count(1)
from dba_tab_privs
where table_name='DBMS_SYS_SQL'
and privilege='EXECUTE'
and grantee='APPS';
COUNT(1)
----------
0
SQL>
Now again run and it was completed for me in couple of minutes
adop phase=cleanup cleanup_mode=full
Comments