A case for DATAFILECOPY FORMAT

I was migrating several databases from AWS EC2 non-Nitro based instances to the Nitro-based ones when I came across one issue with Oracle Recovery Manager (RMAN). This blog post is about it.
The high-level process of the migration was as follows:

1. Attach a new ASM diskgroup to the host that is to be migrated
2. Make an initial level 0 copy of the database
3. Roll forward the copy as many times as needed using an incremental level 1 backup
4. When it's time to switch to the new server, roll forward the copy once again, switch logfile, backup all archivelogs covering the last backup, dismount the ASM diskgroup, mount it on the new server, and open the database (there are also controlfile and spfile copies as well as some extra steps specific to that environment)

I would rather use a physical standby or Golden Gate than that meticulously designed process I developed, albeit those alternatives were ruled out since they would require additional licenses.
At the end of the day, the final downtime was less than 30 minutes as almost everything was automated using Ansible.

I ran that procedure several times in non-Production instances without any issues, however, I got a missing file when I performed the same steps in the Production instance.
Here is how that happened.
The diskgroup configuration is the following:

DATA - db_create_file_dest
FRA - db_recovery_file_dest
MIGR - the transient ASM diskgroup to keep image copies

Let's setup a test tablespace:

SQL> create tablespace test_ts;

Tablespace created.

Make a copy of it:

RMAN> backup as copy incremental level 0 format '+MIGR' tablespace pdb:test_ts tag migr;

Starting backup at 10.06.2019 21:14:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.276.1010610875
output file name=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.256.1010610893 tag=MIGR RECID=6 STAMP=1010610895
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 10.06.2019 21:14:59

Then add a datafile to that tablespace:

SQL> alter tablespace test_ts add datafile;

Tablespace altered.

The final backup/recover block:

RMAN> run {
  backup incremental level 1 format '+MIGR' for recover of copy with tag migr tablespace pdb:test_ts;
  recover copy of tablespace pdb:test_ts with tag migr;
}2> 3> 4>

Starting backup at 10.06.2019 21:16:42
using channel ORA_DISK_1
no parent backup or copy of datafile 17 found
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00016 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.276.1010610875
channel ORA_DISK_1: starting piece 1 at 10.06.2019 21:16:42
channel ORA_DISK_1: finished piece 1 at 10.06.2019 21:16:43
piece handle=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/BACKUPSET/2019_06_10/nnndn1_migr_0.258.1010611003 tag=MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.277.1010610945
output file name=+FRA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.265.1010611003 tag=MIGR RECID=7 STAMP=1010611006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10.06.2019 21:16:46

Despite the fact that the format was set to '+MIGR', the copy of the new added datafile was put to the FRA:

RMAN> list copy tag migr;

specification does not match any control file copy in the repository
specification does not match any archived log in the repository
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time            Sparse
------- ---- - ------------------- ---------- ------------------- ------
8       16   A 10.06.2019 21:16:47 1468031    10.06.2019 21:16:42 NO
        Name: +MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.256.1010610893
        Tag: MIGR
        Container ID: 3, PDB Name: PDB

7       17   A 10.06.2019 21:16:46 1468032    10.06.2019 21:16:43 NO
        Name: +FRA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.265.1010611003
        Tag: MIGR
        Container ID: 3, PDB Name: PDB

That is pretty much the same issue that I encountered while doing a test migration of that multi-terabyte database in the Production system - a few datafiles have been added between the initial level 0 and the subsequent level 1 copies.
It is the case when the DATAFILECOPY FORMAT clause can be used:

RMAN> run {
  backup incremental level 1 
    format '+MIGR' for recover of copy with tag migr 
    datafilecopy format '+MIGR'
    tablespace pdb:test_ts;
  recover copy of tablespace pdb:test_ts with tag migr;
}2> 3> 4>

Starting backup at 10.06.2019 21:21:57
using channel ORA_DISK_1
no parent backup or copy of datafile 19 found
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00018 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.277.1010611243
channel ORA_DISK_1: starting piece 1 at 10.06.2019 21:21:57
channel ORA_DISK_1: finished piece 1 at 10.06.2019 21:21:58
piece handle=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/BACKUPSET/2019_06_10/nnndn1_migr_0.259.1010611317 tag=MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.276.1010611299
output file name=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.260.1010611319 tag=MIGR RECID=10 STAMP=1010611321
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10.06.2019 21:22:01

That's just another flexibility that Oracle provides. If you think about it, it makes complete sense - image copies and backupsets can be stored separately.
Another way to specify location for image copies in that case is an explicit channel configuration:

RMAN> run {
  # allocate as many channels as needed
  allocate channel c1 device type disk format '+MIGR';
  backup incremental level 1 for recover of copy with tag migr tablespace pdb:test_ts;
  recover copy of tablespace pdb:test_ts with tag migr;
}2> 3> 4> 5>

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=94 device type=DISK

Starting backup at 10.06.2019 21:33:43
no parent backup or copy of datafile 21 found
channel c1: starting incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00020 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.276.1010611951
channel c1: starting piece 1 at 10.06.2019 21:33:44
channel c1: finished piece 1 at 10.06.2019 21:33:45
piece handle=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/BACKUPSET/2019_06_10/nnndn1_migr_0.256.1010612025 tag=MIGR comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting datafile copy
input datafile file number=00021 name=+DATA/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.277.1010612011
output file name=+MIGR/ORCL/8AAFC31944116B0CE0554A7F9DE2B2FD/DATAFILE/test_ts.261.1010612025 tag=MIGR RECID=13 STAMP=1010612028
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10.06.2019 21:33:48

ORA-01031 select V$RESTORE_POINT in PL/SQL

It is a well known fact that V$RESTORE_POINT requires special handling, namely the SELECT_CATALOG_ROLE should be granted to a low-privileged user trying to access this view.
I had several PL/SQL units working with V$RESTORE_POINT in a 12.1 database. Those units were owned by a user that has SELECT_CATALOG_ROLE.
Once I upgraded the database to 12.2, those units stopped working and I started getting an infamous ORA-1031 error.
This blog post is about how I fixed that issue for definer rights program units.

Here is a simple test case demonstrating the initial ORA-1031 error:

SYS@CDB$ROOT> create restore point rp_test;

Restore point created.

SYS@CDB$ROOT> alter session set container=pdb;

Session altered.

SYS@PDB> grant connect, create procedure to tc identified by tc;

Grant succeeded.

SYS@PDB> grant read on v_$restore_point to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> create or replace procedure p_test
  2  is
  3  begin
  4    for test_rec in (
  5      select *
  6        from v$restore_point)
  7    loop
  8      dbms_output.put_line(test_rec.name);
  9    end loop;
 10  end;
 11  /

Procedure created.

TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC.P_TEST", line 4
ORA-06512: at line 1

Despite the fact that the user TC does have the READ privilege on V_$RESTORE_POINT, it still is not able to access it.
Till 12.2 it was enough to grant SELECT_CATALOG_ROLE to the owner of a program unit to avoid the error:

SYS@PDB> grant select_catalog_role to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
RP_TEST

PL/SQL procedure successfully completed.

It is not the case anymore in 12.2 and subsequent versions which I tested: 18c and 19c.
The output from 19c is below:

SYS@PDB> grant select_catalog_role to tc;

Grant succeeded.

SYS@PDB> conn tc/tc@localhost/pdb
Connected.
SYS@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TC.P_TEST", line 5
ORA-06512: at "TC.P_TEST", line 5
ORA-06512: at line 1

BTW, the line 'ORA-06512: at "TC.P_TEST", line 5' is reported twice, and 19c shows a slightly different errorstack than 12.1.
The following solution works in 12.2 on:

SYS@PDB> grant select_catalog_role to procedure tc.p_test;

Grant succeeded.

SYS@PDB>
SYS@PDB> conn tc/tc@localhost/pdb
Connected.
TC@PDB>
TC@PDB> set serverout on
TC@PDB>
TC@PDB> exec p_test
RP_TEST

PL/SQL procedure successfully completed.

The need for having the SELECT_CATALOG_ROLE granted to the user in 12.1 does not make much sense as roles do not work in named PL/SQL definer rights program units. I am not talking about roles granted to PL/SQL units here.
Therefore, the "new" behavior requiring the role to be granted to PL/SQL units appears to be more proper and logical.

While working on this issue, I was tinkering with gdb a little bit in an attempt to find an explanation to that SELECT_CATALOG_ROLE requirement - that role is not coming from V$-views as it was said in the blogpost which I referred before.
It turns out that role is used in Oracle code:

(gdb) disassemble kccxrsp
Dump of assembler code for function kccxrsp:
   0x000000000a225740 <+0>:     xchg   %ax,%ax
   0x000000000a225742 <+2>:     push   %rbp
   0x000000000a225743 <+3>:     mov    %rsp,%rbp
   0x000000000a225746 <+6>:     sub    $0x60,%rsp
   0x000000000a22574a <+10>:    mov    %rbx,-0x58(%rbp)
   0x000000000a22574e <+14>:    mov    %rdx,%rbx
..skip..
   0x000000000a2257e5 <+165>:   mov    $0xdda7b48,%edi
   0x000000000a2257ea <+170>:   mov    $0x13,%esi
   0x000000000a2257ef <+175>:   callq  0x859bd90 <kzsrol>
..skip..
---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) x/s 0xdda7b48
0xdda7b48:      "SELECT_CATALOG_ROLE"

GV$RESTORE_POINT is based on x$kccrsp and x$kccnrs. The former is seems to be accessed through the kccxrsp function.
kccxrsp calls kzsrol to perform extra security checks and passes SELECT_CATALOG_ROLE to it.

TL;DR: V$-views are really special views (i.e. no read consistency) and V$RESTORE_POINT has its own little peculiarity among them.
Not only does it require to have the SELECT_CATALOG_ROLE granted to a non-administrative user but also the definer rights PL/SQL unit owned by such a user should have that role granted as well.

OEM Target Version not updated after applying RU patch

After applying the 12.2.0.1.190416 Release Update (RU) patch, I noticed that the target version had not been updated:

I searched through My Oracle Support (MOS) and found a few similar issues where it was recommended to refresh the configuration of the host in question.
Thus, I performed the refresh operation for both the host configuration and the database configuration, yet the version was still the old one.

Here are the targets that are registered on the problem host:

[oraagent@oracle-sandbox bin]$ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
[oracle-sandbox.domain, host]
[oracle-sandbox.domain:3872, oracle_emd]
[+ASM_oracle-sandbox.domain, osm_instance]
[OraDB12Home1_2_oracle-sandbox.domain_3696, oracle_home]
[OraHome1Grid_1_oracle-sandbox.domain_6710, oracle_home]
[agent13c1_3_oracle-sandbox.domain_5948, oracle_home]
[has_oracle-sandbox.domain, has]
[OraGI12Home1_4_oracle-sandbox.domain_67, oracle_home]
[BOXCDB_oracle-sandbox.domain, oracle_database]
[BOXCDB_oracle-sandbox.domain_CDB$ROOT, oracle_pdb]
[BOXCDB_oracle-sandbox.domain_BOXPDB, oracle_pdb]

I ran the following command after which the issue was resolved:

[oraagent@oracle-sandbox bin]$ ./emctl reload agent dynamicproperties BOXCDB_oracle-sandbox.domain:oracle_database
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD recompute dynprops completed successfully

The correct version finally appeared on the database page:

Concatenation and filter subqueries oddity

While investigating a poor query performance issue, I came across the following Real-Time SQL Monitoring report from a 12.1 database:

Global Stats
========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | PL/SQL  | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Time(s) | Calls |  Gets  | Reqs | Bytes |
========================================================================================
|    3041 |     299 |     2742 |        0.00 |      29 |    21 |    27M |  10M |  79GB |
========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=511311330)
========================================================================================================================================================================================================
| Id |                    Operation                     |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity |        Activity Detail        |
|    |                                                  |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |          (# samples)          |
========================================================================================================================================================================================================
|  0 | SELECT STATEMENT                                 |                 |         |       |      2822 |    +93 |     1 |     2044 |       |       |       |     1.65 | Cpu (39)                      |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file sequential read (9)   |
|  1 |   HASH GROUP BY                                  |                 |       1 |       |         1 |  +2914 |     1 |     2044 |       |       |    1M |          |                               |
|  2 |    CONCATENATION                                 |                 |         |       |      2911 |     +4 |     1 |        0 |       |       |       |          |                               |
|  3 |     FILTER                                       |                 |         |       |           |        |     1 |          |       |       |       |          |                               |
|  4 |      FILTER                                      |                 |         |       |           |        |     1 |          |       |       |       |          |                               |
|  5 |       PARTITION LIST SINGLE                      |                 |       1 |   34M |           |        |       |          |       |       |       |          |                               |
|  6 |        TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | DRIVING_TAB     |       1 |   34M |           |        |       |          |       |       |       |          |                               |
|  7 |         INDEX RANGE SCAN                         | DRIVING_TAB_I   |     36M |  368K |           |        |       |          |       |       |       |          |                               |
|  8 |      INDEX RANGE SCAN                            | DICT_PK         |       1 |     3 |      2911 |     +4 |  334K |     304K |  1169 |   9MB |       |     0.24 | Cpu (5)                       |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file sequential read (2)   |
|  9 |     FILTER                                       |                 |         |       |      2911 |     +4 |     1 |    42816 |       |       |       |     0.03 | Cpu (1)                       |
| 10 |      FILTER                                      |                 |         |       |      2911 |     +4 |     1 |     450K |       |       |       |          |                               |
| 11 |       PARTITION LIST SINGLE                      |                 |       1 |   19M |      2911 |     +4 |     1 |     450K |       |       |       |          |                               |
| 12 |        TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | DRIVING_TAB     |       1 |   19M |      2915 |     +0 |     1 |     450K |   10M |  79GB |       |    88.71 | Cpu (72)                      |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file parallel read (2453)  |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file scattered read (5)    |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file sequential read (55)  |
| 13 |         INDEX RANGE SCAN                         | DRIVING_TAB_I   |     20M | 63566 |      2911 |     +4 |     1 |      25M | 78518 | 613MB |       |     8.30 | Cpu (5)                       |
|    |                                                  |                 |         |       |           |        |       |          |       |       |       |          | db file sequential read (237) |
| 14 |      INDEX RANGE SCAN                            | DICT_PK         |       1 |     3 |      2911 |     +4 |  334K |     304K |       |       |       |          |                               |
========================================================================================================================================================================================================

It is quite unusual that line 8 has any number of executions and rows at all as there are no rows returned from line 4-7.
It is also suspicious that the certain activity columns precisely matches line 14.
I decided to take a look at that query using row source executions statistics:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |      1 |        |   2044 |00:48:34.11 |      26M|     10M|       |       |          |
|   1 |  HASH GROUP BY                                 |                 |      1 |      1 |   2044 |00:48:34.11 |      26M|     10M|  1553K|   956K| 1364K (0)|
|   2 |   CONCATENATION                                |                 |      1 |        |  42816 |00:44:40.63 |      26M|     10M|       |       |          |
|*  3 |    FILTER                                      |                 |      1 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  4 |     FILTER                                     |                 |      1 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |      PARTITION LIST SINGLE                     |                 |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| DRIVING_TAB     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  7 |        INDEX RANGE SCAN                        | DRIVING_TAB_I   |      0 |     35M|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  8 |     INDEX RANGE SCAN                           | DICT_PK         |    333K|      1 |    304K|00:00:18.49 |    1610K|   1173 |       |       |          |
|*  9 |    FILTER                                      |                 |      1 |        |  42816 |00:44:40.57 |      26M|     10M|       |       |          |
|* 10 |     FILTER                                     |                 |      1 |        |    449K|00:47:48.59 |      25M|     10M|       |       |          |
|  11 |      PARTITION LIST SINGLE                     |                 |      1 |      1 |    449K|00:47:47.97 |      25M|     10M|       |       |          |
|* 12 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| DRIVING_TAB     |      1 |      1 |    449K|00:47:47.34 |      25M|     10M|       |       |          |
|* 13 |        INDEX RANGE SCAN                        | DRIVING_TAB_I   |      1 |     19M|     24M|00:03:59.62 |   79436 |  78518 |       |       |          |
|* 14 |     INDEX RANGE SCAN                           | DICT_PK         |    333K|      1 |    304K|00:00:18.49 |    1610K|   1173 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Again, it does not make any sense that line 8 is reported as being executed. The number of logical reads does not add up either.
A-Time of INDEX RANGE SCAN DICT_PK is the same in lines 8 and 14, so that I would chalk it up to a statistics reporting bug.

I constructed the following test case used to reproduce the issue and tested it in 12.2, 18.3, 19.2 with the same results:

create table t_driving (status char(1), driving_to_inner_id int);
insert into t_driving values ('A', 1);
insert into t_driving values ('B', 2);
create table t_inner(id int);
insert into t_inner select 1 from dual connect by level<=10;

var status varchar2(1)='B'

begin
  for test_rec in (
    select /*+ use_concat(or_predicates(1)) gather_plan_statistics no_unnest(@inner)*/
           *
      from t_driving
     where (:status='B' and status = 'B' or :status='A' and status = 'A')
       and not exists (
             select /*+ qb_name(inner) */
                    null
               from t_inner
              where id = driving_to_inner_id
           )
  )
  loop 
    null;
  end loop;
  for plan_rec in (select * from table(dbms_xplan.display_cursor( format=> 'allstats last')))
  loop
    dbms_output.put_line(plan_rec.plan_table_output);
  end loop;
end;
/

Here is the output of the last block highlighting the plan execution details:

SQL_ID  4n8swkt3cv3sh, child number 0
-------------------------------------
SELECT /*+ use_concat(or_predicates(1)) gather_plan_statistics
no_unnest(@inner)*/ * FROM T_DRIVING WHERE (:B1 ='B' AND STATUS = 'B'
OR :B1 ='A' AND STATUS = 'A') AND NOT EXISTS ( SELECT /*+
qb_name(inner) */ NULL FROM T_INNER WHERE ID = DRIVING_TO_INNER_ID )
Plan hash value: 3020335025
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  CONCATENATION       |           |      1 |        |      1 |00:00:00.01 |      14 |
|*  2 |   FILTER             |           |      1 |        |      0 |00:00:00.01 |       0 |
|*  3 |    FILTER            |           |      1 |        |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL| T_DRIVING |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    TABLE ACCESS FULL | T_INNER   |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  6 |   FILTER             |           |      1 |        |      1 |00:00:00.01 |      14 |
|*  7 |    FILTER            |           |      1 |        |      1 |00:00:00.01 |       7 |
|*  8 |     TABLE ACCESS FULL| T_DRIVING |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  9 |    TABLE ACCESS FULL | T_INNER   |      1 |      1 |      0 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NULL)
3 - filter(:B1='A')
4 - filter("STATUS"='A')
5 - filter("ID"=:B1)
6 - filter( IS NULL)
7 - filter(:B1='B')
8 - filter(("STATUS"='B' AND (LNNVL(:B1='A') OR LNNVL("STATUS"='A'))))
9 - filter("ID"=:B1)

The total number of buffers is 14, which is correct. However, line 5 has also contributed to 7 buffers somehow. Thus there should have been 21 buffers overall.
If line 4 is not executed, as it has starts 0, how come that line 5 is being executed?
I rechecked all the numbers against V$SQL_PLAN_STATISTICS_ALL, V$SQLAREA_PLAN_HASH and other views. The total executions details, such as buffers, were fine - they were all reported as 14.
The only issue was with line 5 - it should have not been reported as being executed or generating any number of logical I/O.
I believe that is a reporting bug - there should be no starts for line 5 at all.

I was curious how new 12.2 OR-Expansion transformation would behave.
Although I knew it could be forced by using an OR_EXPAND hint, I was struggling to get it work, so that I had to resort to optimizer traces to find out why my hints were not honored by the optimizer.
I found the following lines in the trace file that helped solve that issue:

ORE: Predicate chain after QB validity check - SEL$1
(:B1='B' AND "T_DRIVING"."STATUS"='B' OR :B2='A' AND "T_DRIVING"."STATUS"='A') AND  NOT EXISTS (SELECT /*+ NO_UNNES
T QB_NAME ("INNER") */ 0 FROM "T_INNER" "T_INNER")
ORE: Checking validity of disjunct chain
ORE: Bypassed for disjunct chain: No Index or Partition driver found.
ORE: # conjunction chain - 1
ORE: No state generated.

So there is 'No Index or Partition driver found' - that is something that I can work with. It is interesting enough that the Concatenation transformation does not have those restrictions as far as I am aware. At least, it can be forced for the same query with the same objects.

I recreated the driving table using the following commands:

drop table t_driving;
create table t_driving (
  status char(1), 
  driving_to_inner_id int)
partition by list(status) (
  partition values ('A'), 
  partition values ('B'));
insert into t_driving values ('A', 1);
insert into t_driving values ('B', 2);

Then I ran the query below:

begin
  for test_rec in (
    select /*+ or_expand gather_plan_statistics no_unnest(@inner)*/
           *
      from t_driving
     where (:status='B' and status = 'B' or :status='A' and status = 'A')
       and not exists (
             select /*+ qb_name(inner) */
                    null
               from t_inner
              where id = driving_to_inner_id
           )
  )
  loop 
    null;
  end loop;
  for plan_rec in (select * from table(dbms_xplan.display_cursor( format=> 'allstats last')))
  loop
    dbms_output.put_line(plan_rec.plan_table_output);
  end loop;
end;
/

That had the following execution plan:

SQL_ID  16q8nc7mcy3b0, child number 0
-------------------------------------
SELECT /*+ or_expand gather_plan_statistics no_unnest(@inner)*/ * FROM
T_DRIVING WHERE (:B1 ='B' AND STATUS = 'B' OR :B1 ='A' AND STATUS =
'A') AND NOT EXISTS ( SELECT /*+ qb_name(inner) */ NULL FROM T_INNER
WHERE ID = DRIVING_TO_INNER_ID )
Plan hash value: 1055582790
-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |      1 |        |      1 |00:00:00.01 |      44 |
|   1 |  VIEW                     | VW_ORE_95B37148 |      1 |      2 |      1 |00:00:00.01 |      44 |
|   2 |   UNION-ALL               |                 |      1 |        |      1 |00:00:00.01 |      44 |
|*  3 |    FILTER                 |                 |      1 |        |      1 |00:00:00.01 |      44 |
|*  4 |     FILTER                |                 |      1 |        |      1 |00:00:00.01 |      37 |
|   5 |      PARTITION LIST SINGLE|                 |      1 |      1 |      1 |00:00:00.01 |      37 |
|   6 |       TABLE ACCESS FULL   | T_DRIVING       |      1 |      1 |      1 |00:00:00.01 |      37 |
|*  7 |     TABLE ACCESS FULL     | T_INNER         |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  8 |    FILTER                 |                 |      1 |        |      0 |00:00:00.01 |       0 |
|*  9 |     FILTER                |                 |      1 |        |      0 |00:00:00.01 |       0 |
|  10 |      PARTITION LIST SINGLE|                 |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 11 |       TABLE ACCESS FULL   | T_DRIVING       |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 12 |     TABLE ACCESS FULL     | T_INNER         |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( IS NULL)
4 - filter(:B1='B')
7 - filter("ID"=:B1)
8 - filter( IS NULL)
9 - filter(:B1='A')
11 - filter((LNNVL(:B1='B') OR LNNVL("STATUS"='B')))
12 - filter("ID"=:B1)

Notice that there are no executions in line 10 to 12 - that is how it should be.

TL;DR: a Concatenation transformation with filter subqueries may over-report actual execution statistics, so that the totals, such as Buffers, are fine, however, the plan lines related to filter sub-queries are reported as being executed and having the same execution statistics as their corresponding lines in other concatenation branches.
Those subqueries are not really executed but just are reported as being so.
Thankfully, the 12.2 OR-Expansion transformation is not susceptible to that issue - the execution statistics are reported back as zero for those subqueries which are not actually executed because they are eliminated in the parent filter branches.
The code is this article has been tested against 12.2, 18.3, and 19.2.

PL/SQL Create or Replace Optimization

Oracle said in their Student Guide for 12c that a local user cannot utilize system privileges on a common user's schema.
When I initially checked that preposition, I was a little bit puzzled by the following behavior:

TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

TC@PDB>
TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5    null; -- second null
  6  end;
  7  /
create or replace procedure c##tc.p
*
ERROR at line 1:
ORA-01031: insufficient privileges

I drew a proper conclusion back then that Oracle generally tries to be as lazy as possible, so that if it could not do something, it would probably would not do it.
Like in the example above, if Oracle knows that the source code is the same and all PL/SQL settings are the same, why bother? It just reports back as if the code had been compiled when actually Oracle compared the stored source and its PL/SQL settings with whatever a user supplied.
Although it seems a sound approach, I did not have enough proves that Oracle definitely works in such a way with PL/SQL units.
However, last week I listened to an excellent Bryn Llewellyn's one day seminar where we were discussing Edition-Based Redefinition (EBR) topics and Bryn said exactly the same thing - PL/SQL does have an optimization to not recompile stored units when the supplied code is the same (it was roughly something like that).
It rang a bell and made me return to that issue discovered a few years ago.

Let's make an initial setup and reproduce the error:

SYS@CDB$ROOT> conn / as sysdba
Connected.

SYS@CDB$ROOT> create user c##tc identified by oracle;

User created.

SYS@CDB$ROOT> grant connect, create procedure to c##tc container=all;

Grant succeeded.

SYS@CDB$ROOT>
SYS@CDB$ROOT> conn c##tc/oracle@pdb
Connected.
C##TC@PDB>
C##TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

C##TC@PDB>
C##TC@PDB> conn sys/oracle@pdb as sysdba
Connected.
SYS@PDB>
SYS@PDB> grant alter session, connect, create any procedure to tc identified by tc;

Grant succeeded.

SYS@PDB> conn tc/tc@pdb
Connected.
TC@PDB>
TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

TC@PDB>
TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5    null; -- second null
  6  end;
  7  /
create or replace procedure c##tc.p
*
ERROR at line 1:
ORA-01031: insufficient privileges

I reexecute the same create or replace statement with sql trace enabled so as to confirm that it is effectively a noop operation - Oracle does not really recompile that procedure.
While looking into the trace file, the last executed statement is this:

PARSING IN CURSOR #140098350838112 len=54 dep=1 uid=0 oct=3 lid=0 tim=228489468158 hv=696375357 ad='c49fffc0' sqlid='9gq78x8ns3q1x'
select source from source$ where obj#=:1 order by line
END OF STMT

Once I got this, I decided to obtain a short stack of Oracle functions to see what function in Oracle kernel causes that SQL to be executed:

TC@PDB> alter session set events 'sql_trace[sql:9gq78x8ns3q1x] trace("%s\n", shortstack())';

Session altered.
TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

The interesting part in the trace file is below:

kkscsCheckCriteria<-kkscsCheckCursor<-kkscsSearchChildList<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-opiall0<-opikpr<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-kprball<-kqlsrclod<-kqllod_new<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kkx_same_src<-kkpcrt<-opies
kxstGetSqlTraceLevel<-kkscsCheckCriteria<-kkscsCheckCursor<-kkscsSearchChildList<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-opiall0<-opikpr<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-kprball<-kqlsrclod<-kqllod_new<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kkx_s
opiexe<-opiall0<-opikpr<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-kprball<-kqlsrclod<-kqllod_new<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kkx_same_src<-kkpcrt<-opiexe<-opiosq0<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrs
kxstGetSqlTraceLevel<-opiexe<-opiall0<-opikpr<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-kprball<-kqlsrclod<-kqllod_new<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kkx_same_src<-kkpcrt<-opiexe<-opiosq0<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sous

Notice that sequence: kkpcrt -> kkx_same_src I was quite curious about that kkx_same_src as it looked like an explanation of that fictional compilation, so I just disassembled it and found these two assembler instructions below in the output:

gdb $ORACLE_HOME/bin/oracle
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle...(no debugging symbols found)...done.
(gdb) disassemble kkx_same_src
Dump of assembler code for function kkx_same_src:
... skip ...
   0x0000000002b8703f <+479>:   mov    $0x291b,%edi
   0x0000000002b87044 <+484>:   callq  0xceafa20 <dbkdchkeventrdbmserr>

There is an event 0x291b that can influence that function! Let's try this out:

TC@PDB> alter session set events '0x291b level 1';

Session altered.

TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /
create or replace procedure c##tc.p
*
ERROR at line 1:
ORA-01031: insufficient privileges


TC@PDB> alter session set events '0x291b off';

Session altered.

TC@PDB> create or replace procedure c##tc.p
  2  is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

Indeed, it does effect the execution. The next step is to find out what this event is:

[oracle@localhost ~]$ echo 'ibase=16;291B' | bc
10523
[oracle@localhost ~]$ oerr ora 10523
10523, 00000, "force recreate package even if definition is unchanged"
// *Cause:
// *Action:  Set this event only under the supervision of Oracle development
// *Comment: Changes behaviour of create or replace package, procedure,
//           function or type to force recreation of the object even if its new
//           definition exactly matches the old definition.
//           No level number required.

That event was the last missing piece in this puzzle.

TL;DR: as any good software, Oracle tries to do as little work as possible - this is a laziness in its good sense.
After all, the fastest way to do something is to not do it at all.
That what happens here - if there is no need to compile a PL/SQL unit, Oracle can identify it and report back to the user that the PL/SQL has been compiled when really it was not. That optimization is controlled by event 10523.
However, it kicks in early, before other Multitenant related checks, namely Oracle does not check whether a local user actually can compile a stored PL/SQL unit in a common schema.
I believe that is a bug as there should be an ORA-1031 error in the first place, even when the source is the same.
Everything said is applicable only to 12.1.0.2 on which it has been tested.

wget one liners to download data from *.oracle.com without human intervention

When the topic of downloading Oracle software comes up, there is a common misconception that it requires a human intervention to accept the license terms.
Here are wget commands that I use to download data from Oracle sites, such as edelivery.oracle.com, download.oracle.com, and support.oracle.com:

wget \
  --user "<oracleaccount>" \
  --ask-password \
  --load-cookies <(printf '.oracle.com\tTRUE\t/\tFALSE\t0\toraclelicense\taccept-securebackup-cookie') \
  "<https_downloadlink>"

Or its fully automatic version:

wget \
  --user "<oracleaccount>" \
  --password "<password>" \
  --load-cookies <(printf '.oracle.com\tTRUE\t/\tFALSE\t0\toraclelicense\taccept-securebackup-cookie') \
  "<https_downloadlink>"

It has been tested against all of those sites and works fine as of now.
A few examples are below.
edelivery.oracle.com:

wget \
  --user "<oracleaccount>" \
  --ask-password \
  --load-cookies <(printf '.oracle.com\tTRUE\t/\tFALSE\t0\toraclelicense\taccept-securebackup-cookie') \
  "https://edelivery.oracle.com/akam/otn/linux/oracle18c/xe/oracle-database-xe-18c-1.0-1.x86_64.rpm"

download.oracle.com:

wget \
  --user "<oracleaccount>" \
  --ask-password \
  --load-cookies <(printf '.oracle.com\tTRUE\t/\tFALSE\t0\toraclelicense\taccept-securebackup-cookie') \
  "https://download.oracle.com/otn/linux/oracle18c/xe/oracle-database-xe-18c-1.0-1.x86_64.rpm"

support.oracle.com - this one does not require the cookie:

wget \
  --user "<oracleaccount" \
  --ask-password \
  "https://updates.oracle.com/Orion/Services/download/p6880880_180000_Linux-x86-64.zip?aru=22569537&patch_file=p6880880_180000_Linux-x86-64.zip"

Bitmap Conversion to Rowids with Global Indexes

While working with developers on optimizing a query, I discovered a limitation of the "BITMAP AND" operation.
Here is an example demonstrating the issue:

SQL> create table t(
  2    part_col int,
  3    a int,
  4    b int)
  5  partition by list(part_col) (
  6    partition values (0)
  7  )
  8  ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('', 't')

PL/SQL procedure successfully completed.

SQL>
SQL> create index t_a_i on t(a);

Index created.

SQL> create index t_b_i on t(b) local;

Index created.

SQL>
SQL> explain plan for
  2  select /*+ bitmap_tree(t and((a) (b)))*/
  3         *
  4    from t
  5   where a = :1
  6     and b = :2;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1636314338

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |     1 |    39 |     1   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T     |     1 |    39 |     1   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | T_A_I |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"=TO_NUMBER(:2))
   2 - access("A"=TO_NUMBER(:1))

15 rows selected.

Surprisingly, the plan shows just one INDEX RANGE SCAN operation and the expected BITMAP AND did not happen.
Let us see what we get once we make both indexes local:

SQL> drop index t_a_i;

Index dropped.

SQL> drop index t_b_i;

Index dropped.

SQL> create index t_a_i on t(a) local;

Index created.

SQL> create index t_b_i on t(b) local;

Index created.

SQL>
SQL> explain plan for
  2  select /*+ bitmap_tree(t and((a) (b)))*/
  3         *
  4    from t
  5   where a = :1
  6     and b = :2;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3534803727

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |     1 |    39 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE                     |       |     1 |    39 |     3   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T     |     1 |    39 |     3   (0)| 00:00:01 |     1 |     1 |
|   3 |    BITMAP CONVERSION TO ROWIDS             |       |       |       |            |          |       |       |
|   4 |     BITMAP AND                             |       |       |       |            |          |       |       |
|   5 |      BITMAP CONVERSION FROM ROWIDS         |       |       |       |            |          |       |       |
|*  6 |       INDEX RANGE SCAN                     | T_A_I |       |       |     1   (0)| 00:00:01 |     1 |     1 |
|   7 |      BITMAP CONVERSION FROM ROWIDS         |       |       |       |            |          |       |       |
|*  8 |       INDEX RANGE SCAN                     | T_B_I |       |       |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"=TO_NUMBER(:1))
   8 - access("B"=TO_NUMBER(:2))

21 rows selected.

Here it is. Once I made both indexes local, the "BITMAP AND" operation showed up.
Initially, I thought that it might have been related to different rowid's formats used for global and local indexes.
I just inserted one row to the table and dumped the leaf block for the global and local indexes:

$ grep 'col ' global.trc local.trc
global.trc:col 0; len 2; (2):  c1 02
global.trc:col 1; len 10; (10):  00 01 67 82 02 80 13 69 00 00
local.trc:col 0; len 2; (2):  c1 02
local.trc:col 1; len 6; (6):  02 80 13 69 00 00

You see that 'col 1' for the local index has a rowid in it: "len 6; (6): 02 80 13 69 00 00",
whereas the corresponding column for the global index has 10 bytes in length: "len 10; (10): 00 01 67 82 02 80 13 69 00 00"
In which the leading 4 bytes refer to the object id.
I also performed additional tests trying all possible combinations of indexes T_A_I and T_B_I making them global or local.
The only case when I was getting a "BITMAP AND" was the case when both indexes were local.
Changing the hint to INDEX_COMBINE has not changed anything.

Having gathered all of those results, I searched My Oracle Support (MOS) and found the explanation:
Bug 8787683 : SUPPORT GLOBAL BITMAP INDEXES AND BITMAP CONVERSION ON GLOBAL BTREE INDEXES
There is, indeed, the enhancement request raised in 2009.

The source query was rewritten to combine both indexes early and use filtered rowids to access the base table:

SQL> explain plan for
  2  select /*+ unnest(@subq) no_use_hash_aggregation(@subq)*/
  3         *
  4    from t
  5   where rowid in (
  6           select /*+ qb_name(subq)*/
  7                  rid
  8             from (select rowid rid
  9                     from t
 10                    where a = :1
 11                    union all
 12                   select rowid
 13                     from t
 14                    where b = :2)
 15            group by rid
 16            having count(*)=2)
 17  ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4066617294

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    63 |     5  (40)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS               |          |     1 |    63 |     5  (40)| 00:00:01 |       |       |
|   2 |   VIEW                      | VW_NSO_1 |     2 |    24 |     3  (34)| 00:00:01 |       |       |
|*  3 |    FILTER                   |          |       |       |            |          |       |       |
|   4 |     SORT GROUP BY           |          |     1 |    24 |     3  (34)| 00:00:01 |       |       |
|   5 |      VIEW                   |          |     2 |    24 |     2   (0)| 00:00:01 |       |       |
|   6 |       UNION-ALL             |          |       |       |            |          |       |       |
|*  7 |        INDEX RANGE SCAN     | T_A_I    |     1 |    25 |     1   (0)| 00:00:01 |       |       |
|   8 |        PARTITION LIST SINGLE|          |     1 |    25 |     1   (0)| 00:00:01 |     1 |     1 |
|*  9 |         INDEX RANGE SCAN    | T_B_I    |     1 |    25 |     1   (0)| 00:00:01 |     1 |     1 |
|  10 |   TABLE ACCESS BY USER ROWID| T        |     1 |    51 |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)=2)
   7 - access("A"=TO_NUMBER(:1))
   9 - access("B"=TO_NUMBER(:2))

Notice the "SORT GROUP BY" operation in line 4 - it guarantees that the set of ROWIDs will be sorted, so that we will never return to a block that was previously visited once we switched to another.

TL;DR: the "BITMAP AND" operation has some restrictions when Global indexes are involved: Bug 8787683 : SUPPORT GLOBAL BITMAP INDEXES AND BITMAP CONVERSION ON GLOBAL BTREE INDEXES
The code was tested against 12.1.0.2 and 19c available on https://livesql.oracle.com