When I initially checked that preposition, I was a little bit puzzled by the following behavior:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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 |
While looking into the trace file, the last executed statement is this:
1 2 3 | 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 |
1 2 3 4 5 6 7 8 9 10 11 | 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. |
1 2 3 4 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | gdb $ORACLE_HOME/bin/oracle GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7 Copyright (C) 2013 Free Software Foundation, Inc. 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: 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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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. |
1 2 3 4 5 6 7 8 9 10 | [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. |
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.