ouch: ORA-4043 DROPPING PACKAGE WHEN SAME PACKAGE EXISTS IN ANOTHER USER

this one is really nasty (Bug 18097476, 12.1.0.1 on Linux x86-64 )

test-case (although slightly different from the description of the bug):

connect / as sysdba
drop user pp cascade;
drop user ppp cascade;
drop user pppp cascade;
create user pp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pp;
connect pp/"Welcome1$"

create or replace package p1 as
  type t1 is record ( a number, b varchar2(10));
  type tt1 is table of t1;
  function pipelined return tt1 pipelined;
end p1;
/

create or replace package body p1 as
function pipelined return tt1 pipelined
as
  l t1;
  lt tt1 := tt1();
begin
  for i in 1..10
  loop
    lt.extend;
    l.a := i;
    l.b := 'aaaa';
    lt(lt.last) := l;
    pipe row(lt(lt.last));
  end loop;
  return;
end pipelined;
end p1;
/
select * from table (p1.pipelined);

connect / as sysdba
select owner,object_name,object_type,object_id from dba_objects where object_name = 'P1';
select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

create user ppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to ppp;

create user pppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pppp;

create or replace directory dir_tmp as '/home/oracle/';

host rm -f /home/oracle/dmp.dmp
host expdp userid="'/ as sysdba'" schemas=pp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:ppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:pppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log

select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

the last select shoud return something similar to this:

OWNER	  OBJECT_NAME			 OBJECT_TYPE OBJECT_ID
--------- ------------------------------ ----------- ----------
PP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE	          20223
PP	  SYS_PLSQL_62B6CB68_9_1	 TYPE	          20222
PP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20224
PPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20273
PPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20272
PPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20274
PPPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20301
PPPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20300
PPPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20302

if now, for whatever reason, someone drops one of the “not” dummy_1 types:

drop type SYS_PLSQL_62B6CB68_24_1;

… the result is, that you can not drop or compile the package anymore:

alter package p1 compile;
alter package p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

or, try to compile the package for the second schema without droppping a type before (does not happen on the third one :) ):

SYS@dbs101> alter package ppp.p1 compile;
alter package ppp.p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

suprisingly this does not happen with 2 schemas in my case. it starts when a third schema is present.

fyi: oracle creates this SY_PLSQL* as soon as you create a package which contains pipeplined functions which return types defined in the package (bad sentence, I know :) ).

UPDATE 2014-JUL-18:Patch 18097476 is currently available on top of 12.1.0.1 for Linux x86-64.
UPDATE 2015-FEB-05:Same issue reproduces on 12.1.0.2 (with PSU2 applied) on Linux x86-64. No patch available currently
UPDATE 2015-FEB-09:Patch 19504744 is vailable for 12.1.0.2