Database Upgrade to 12c & higher ,fails with Error "ORA-65040" when GRANT SELECT ON aq$_unflushed_dequeues & "SYS.DBMS_PRVTAQIM" (Doc ID 2823117.1)

Oracle Database Release 19 Post-Upgrade Status Tool 11-03-2021 23:47:1

Oracle Server
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at line 26
ORA-06512: at line 18
ORA-06512: at line 26 ORA-06512: at line 18
Oracle Server INVALID 19.8.0.0.0 00:16:12

Oracle XML Database
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at line 35
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 855
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 789
ORA-06512: at line 24
Oracle XML Database INVALID 19.8.0.0.0 00:02:21

Elapsed: 00:00:00.02
23:28:02 SQL>
23:28:02 SQL> Rem =========================================================================
23:28:02 SQL> Rem BEGIN AQ Correct grant for unflushed_dequeues
23:28:02 SQL> Rem =========================================================================
23:28:02 SQL>
23:28:02 SQL> DECLARE
23:28:02 2 stmt VARCHAR2(500);
23:28:02 3 BEGIN
23:28:02 4
23:28:02 5 -- only when it has flags multiple deq(1), multi-cosnumer(8)
23:28:02 6 -- and 10i style queue tables(8192)
23:28:02 7 FOR cur_rec IN (
23:28:02 8 SELECT distinct(schema)
23:28:02 9 FROM system.aq$_queue_tables
23:28:02 10 WHERE bitand(flags, 1)=1 and
23:28:02 11 bitand(flags, 8)=8 and
23:28:02 12 bitand(flags, 8192)=8192
23:28:02 13 )
23:28:02 14 LOOP
23:28:02 15 BEGIN
23:28:02 16 stmt := 'GRANT SELECT ON aq$_unflushed_dequeues to ' ||
23:28:02 17 dbms_assert.enquote_name(cur_rec.schema, FALSE);
23:28:02 18 EXECUTE IMMEDIATE stmt;
23:28:02 19 END;
23:28:02 20 END LOOP;
23:28:02 21
23:28:02 22 EXCEPTION
23:28:02 23 WHEN OTHERS THEN
23:28:02 24 DBMS_SYSTEM.ksdwrt(DBMS_SYSTEM.trace_file,'error in aq grant:' ||
23:28:02 25 sqlcode);
23:28:02 26 RAISE;
23:28:02 27
23:28:02 28 END;
23:28:02 29 /
DECLARE
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at line 26
ORA-06512: at line 18

23:35:31 SQL> DECLARE
23:35:31 2 altvstmt VARCHAR2(1000);
23:35:31 3 BEGIN
23:35:31 4
23:35:31 5 FOR cur_rec IN (
23:35:31 6 SELECT distinct t.schema, t.name, t.flags
23:35:31 7 FROM system.aq$_queue_tables t, system.aq$_queues q
23:35:31 8 WHERE t.objno = q.table_objno and NVL(q.sharded,0) =0
23:35:31 9 )
23:35:31 10 LOOP
23:35:31 11 BEGIN
23:35:31 12 BEGIN
23:35:31 13 altvstmt := 'alter view
23:35:31 14 '||dbms_assert.enquote_name(cur_rec.schema,FALSE) ||'.'||
23:35:31 15 dbms_assert.enquote_name('AQ$_'||cur_rec.name ||'_F',FALSE) ||
23:35:31 16 ' compile';
23:35:31 17 execute immediate altvstmt;
23:35:31 18 EXCEPTION WHEN OTHERS THEN
23:35:31 19 null;
23:35:31 20 END;
23:35:31 21
23:35:31 22 IF cur_rec.name != 'AQ$DEF$_AQCALL' and cur_rec.name != 'DEF$_AQERROR' THEN
23:35:31 23 IF bitand(cur_rec.flags, 1) = 1 THEN -- multi-consumer queue
23:35:31 24 sys.dbms_prvtaqim.create_base_view(cur_rec.schema, cur_rec.name, cur_rec.flags);
23:35:31 25 ELSE -- singleconsumer queue
23:35:31 26 sys.dbms_aqadm_sys.create_base_view(cur_rec.schema, cur_rec.name, cur_rec.flags);
23:35:31 27 END IF;
23:35:31 28 END IF;
23:35:31 29 END;
23:35:31 30 END LOOP;
23:35:31 31 EXCEPTION
23:35:31 32 WHEN OTHERS THEN
23:35:31 33 DBMS_SYSTEM.ksdwrt(DBMS_SYSTEM.trace_file,
23:35:31 34 'error in unsharded view creation' || sqlcode);
23:35:31 35 RAISE;
23:35:31 36 END;
23:35:31 37 /
DECLARE
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at line 35
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 855
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 789
ORA-06512: at line 24
ORA-06512: at line 24