Upgrade to 12.2 is slow on a particular query where revoke operations are running as part of Final Upgrade Actions (Doc ID 2637265.1)

Oracle Database 12.2 Post-Upgrade Status Tool           12-27-2019 21:33:45

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:14:36
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:53
Oracle XML Database                       VALID      12.2.0.1.0  00:00:00
Final Actions                                                    05:20:42                                       <<<<<<<<<<<<<<
Post Upgrade                                                     00:01:53

Total Upgrade Time: 05:39:21

15:53:12 SQL> 15:53:12 SQL> Rem
15:53:12 SQL> Rem $Header: rdbms/admin/catupstr.sql /st_rdbms_12.2.0.1.0/1 2016/10/19 22:34:03 amunnoli Exp $

Ecoulé : 00 :00 :00.01
16:11:10 SQL>
16:11:10 SQL> Rem =========================================================================
16:11:10 SQL> Rem BEGIN BUG 20888348 - REVOKE SELECT ON DICTIONARY VIEWS FROM PUBLIC AFTER
16:11:10 SQL> Rem UPGRADE. REVOKE WILL DONE ONLY ON THOSE VIEWS WHICH
16:11:10 SQL> Rem HAS READ PRIVILEGE GRANTED ON THEM TO PUBLIC TOO.
16:11:10 SQL> Rem Combination of dbms_assert and ALL_USERS leads to deadlock and hence
16:11:10 SQL> Rem ALL_USERS is revoked separately.
16:11:10 SQL> Rem =========================================================================
16:11:10 SQL> declare
16:11:10 2 TYPE revoke_array_type IS TABLE OF VARCHAR2(300) INDEX BY pls_integer;
16:11:10 3 v_rev_array revoke_array_type;
16:11:10 4 v_dml_str VARCHAR2 (300);
16:11:10 5 capitalize BOOLEAN;
16:11:10 6 begin
16:11:10 7 capitalize := FALSE;
16:11:10 8 with part1 as (
16:11:10 9 select o.name object, u1.name owner, o.obj#
16:11:10 10 from obj$ o, user$ u, table_privilege_map tpm, objauth$ oa, user$ u1
16:11:10 11 where o.type# in (2,4) -- TABLE/VIEW
16:11:10 12 and bitand (o.flags, 4194304) = 4194304 -- Oracle Maintained
16:11:10 13 and u.name = 'PUBLIC'
16:11:10 14 and o.obj# = oa.obj#
16:11:10 15 and o.owner# = u1.user#
16:11:10 16 and oa.grantee# = u.user#
16:11:10 17 and oa.privilege# = tpm.privilege
16:11:10 18 and tpm.name = 'SELECT'),
16:11:10 19 part2 AS (
16:11:10 20 select o.name object, u1.name owner, o.obj#
16:11:10 21 from obj$ o, user$ u, table_privilege_map tpm, objauth$ oa, user$ u1
16:11:10 22 where o.type# in (2,4) -- TABLE/VIEW
16:11:10 23 and bitand (o.flags, 4194304) = 4194304 -- Oracle Maintained
16:11:10 24 and u.name = 'PUBLIC'
16:11:10 25 and o.obj# = oa.obj#
16:11:10 26 and o.owner# = u1.user#
16:11:10 27 and oa.grantee# = u.user#
16:11:10 28 and oa.privilege# = tpm.privilege
16:11:10 29 and tpm.name = 'READ')
16:11:10 30 SELECT *
16:11:10 31 BULK COLLECT
16:11:10 32 INTO v_rev_array
16:11:10 33 FROM
16:11:10 34 (SELECT dbms_assert.enquote_name (part1.owner,capitalize)
16:11:10 35 ||'.'||
16:11:10 36 dbms_assert.enquote_name (part1.object,capitalize)
16:11:10 37 FROM part1,
16:11:10 38 part2
16:11:10 39 where part1.object = part2.object
16:11:10 40 and part1.owner = part2.owner
16:11:10 41 MINUS
16:11:10 42 SELECT dbms_assert.enquote_name (part1.owner,capitalize)
16:11:10 43 ||'.'||
16:11:10 44 dbms_assert.enquote_name (part1.object,capitalize)
16:11:10 45 FROM PART1, objauth$ oa1
16:11:10 46 where oa1.obj# = part1.obj# and
16:11:10 47 oa1.privilege# not in (9,17)
16:11:10 48 MINUS
16:11:10 49 SELECT dbms_assert.enquote_name(part1.owner,capitalize)
16:11:10 50 ||'.'||
16:11:10 51 dbms_assert.enquote_name(part1.object,capitalize)
16:11:10 52 FROM part1
16:11:10 53 WHERE part1.object = 'ALL_USERS'
16:11:10 54 and part1.owner = 'SYS');
16:11:10 55 if (v_rev_array.count > 0)
16:11:10 56 then
16:11:10 57 for i in v_rev_array.first..v_rev_array.last loop
16:11:10 58 begin
16:11:10 59 v_dml_str:= 'revoke select on '
16:11:10 60 || dbms_assert.qualified_sql_name (v_rev_array(i))
16:11:10 61 || ' from public';
16:11:10 62 execute immediate v_dml_str;
16:11:10 63 exception when OTHERS then
16:11:10 64 NULL;
16:11:10 65 end;
16:11:10 66 end loop;
16:11:10 67 end if;
16:11:10 68 commit;
16:11:10 69 exception when OTHERS then
16:11:10 70 NULL;
16:11:10 71 end;
16:11:10 72 /

Procédure PL/SQL terminée avec succès.

Elapsed : 05 :18 :48.11                                                   <<<<<<<<<<<<<<<<<
21:29:58 SQL>