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
Daniel Westermann's Blog 