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

May 27, 2014 — 3 Comments

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

Advertisements

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

  1. 

    Can you tell me if this issue also exists on 12.1.0.2.0? if so, do you know the bug and patch numbers? That would be extremely helpful.

    I am a systems analyst/developer, not a DBA. I encountered this issue a few months ago when we were on 12.1.0.1.0, and at that time our DBAs applied the above patch.

    The issue has recurred after our databases were rehosted. We are now on 12.1.0.2.0

    • 

      Hi,

      did a quick test on 12.1.0.2 (which PSU2 applied) and yes, this issue reproduces. Just take the test-case from the post above to check it on your side.

      Seems there is no patch available in mos for 12.1.0.2.

      The bug number is 18097476

      Cheers, Daniel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s