Ok, Let me start on my very first blog entry. Firstly I should say I am totally new to blogging so it may take a while before I settle on a format and style.
I want to try and combine two subjects in this entry:
- How you can try and investigate Oracle commands to see what they do, where Oracle stores it’s dictionary data and how you can get more information about an Oracle error.
- Trying to be topical, I thought I would use the example of enabling the Oracle 11gR2 Editions feature on a complex schema to allow multi versioning of code and hot releases.
One of the first issues you may find when you try and enable the Oracle 11gr2 Editions feature on a complex schema is the following error:
SQL> alter user test_codea enable editions;
alter user test_codea enable editions
*
ERROR at line 1:
ORA-38819: user TEST_CODEA owns one or more objects whose type is editionable
and that have noneditioned dependent objects
This is a very simple test script to cause the error (run on 11.2.0.1.1)
set echo on
rem set up two demo users called test_codea and test_codeb
connect / as sysdba
drop user test_codea cascade;
drop user test_codeb cascade;
create user test_codea identified by test_codea;
create user test_codeb identified by test_codeb;
grant create session,resource to test_codea;
grant create session,resource to test_codeb;
connect test_codea/test_codea
create or replace package ca as
procedure a;
end;
/
create or replace package body ca as
procedure a is
begin
null;
end;
end;
/
grant execute on ca to test_codeb;
connect test_codeb/test_codeb
create or replace package cb as
procedure b;
end;
/
create or replace package body cb as
procedure b is
begin
test_codea.ca.a; -- This procedure makes a call back to the procedure test_codeA
end;
end;
/
rem confirm the code works
execute cb.b;
connect / as sysdba
rem now try and enable the 11gR2 Editions feature on the test_codeA user
alter user test_codea enable editions;
So what does the error mean and what can you do about it:
ORA-38819: user TEST_CODEA owns one or more objects whose type is editionable
and that have noneditioned dependent objects
Well, Oracle is kindly telling you that one or more objects in the schema is dependent on something which is not enabled for Editions, or another non Editioned schema will depend on your objects….. OR on objects which can never support Editions feature.
Well, kind Oracle gives as a nice work around to the issue…… or do they:
SQL> alter user test_codea enable editions force;
User altered.
SQL> connect test_codeb/test_codeb
Connected.
SQL> execute cb.b;
BEGIN cb.b; END;
*
ERROR at line 1:
ORA-04063: package body "TEST_CODEB.CB" has errors
ORA-06508: PL/SQL: could not find program unit being called: "TEST_CODEB.CB"
ORA-06512: at line 1
Oracle allows us to FORCE a user to be enabled for Editions however this can be dangerous because it can really break a complete schema in a very big way.
In the case of my simple example, there is a very simple solution, just enable Editions on the test_userB first so that when we enable Editions on test_userA both can now be versioned under the same Edition.
SQL> alter user test_codea enable editions;
alter user test_codea enable editions
*
ERROR at line 1:
ORA-38819: user TEST_CODEA owns one or more objects whose type is editionable
and that have noneditioned dependent objects
SQL>
SQL>
SQL>
SQL> alter user test_codeb enable editions;
User altered.
SQL> alter user test_codea enable editions;
User altered.
SQL> connect test_codeb/test_codeb
Connected.
SQL> execute cb.b;
PL/SQL procedure successfully completed.
BUT, the above example is very simple case, what happens when you have dependency on objects which cannot be Editioned (ever!). There are a number of cases where objects can not be editioned and these objects must be moved out of the current schema to a non editioned schema. Examples of these objects are things like “user defined indexes” which include a user written plsql function, restrictions exist for the objects relating to Advanced Queues.
So, back to the other point of this post, how can we find out which objects are causing us problems so that we can fix or move them to another schema. Back to the good old 10046 trace. Lets trace the command and find out how Oracle is deciding which objects are invalid and need fixing before Editions can be enabled.
SQL> alter session set events '10046 trace name context forever, level 1';
Session altered.
SQL>
SQL> alter user test_codea enable editions;
alter user test_codea enable editions
*
ERROR at line 1:
ORA-38819: user TEST_CODEA owns one or more objects whose type is editionable
and that have noneditioned dependent objects
So if we look at the trace file we find the following internal Oracle sql statement along with a bunch of other stuff:
PARSING IN CURSOR #2 len=487 dep=1 uid=0 oct=3 lid=0 tim=1273060065216252 hv=305533609 ad='271fb5cf8' sqlid='btgrjvh93c4p9'
select d.owner#, u.name, d.name, d.namespace, d.stime from obj$ d, dependency$ dep, obj$ p, user$ u where d.obj# = dep.d_obj# and p.obj# = dep.p_obj# and d.remoteowner is null and p.owner# = :1 and d.owner# = u.user# and p.type# in (4,5,7,8,9,10,11,12,13,14,22,87) and ((u.type# != 2 and bitand(u.spare1, 16) = 0 and u.user# != p.owner#) or (d.type# not in (4,5,7,8,9,10,11,12,13,14,22,87)))
END OF STMT
PARSE #2:c=1000,e=758,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1273060065216251
EXEC #2:c=10999,e=10924,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1111485757,tim=1273060065227267
FETCH #2:c=35994,e=37823,p=0,cr=872,cu=0,mis=0,r=1,dep=1,og=4,plh=1111485757,tim=1273060065265189
In the above trace output, we can see the SQL statement which Oracle runs to test for invalid objects which prevent the user Editions feature being enabled (In fact in my simple case, I can see “r=1″ on the FETCH meaning my simple case found 1 row matching invalid objects.
So, I can take that SQL statement and run it to find all objects which would cause problems enabling Editions and can fixed the problems in advance of trying to enable the editions feature:
Cleaned up version of the sql statement and output (Note: avoid running statements you extract this way on production systems unless you are really sure they will not have any adverse effects!)
connect / as sysdba
set echo on
rem find the user_id for the schema
select user_id from sys.dba_users where username = '&USERNAME'
/
set linesize 132
rem provide the user_id to the following sql statement
select d.owner#,
u.name,
d.name,
d.namespace,
d.stime
from obj$ d,
dependency$ dep,
obj$ p, user$ u
where d.obj# = dep.d_obj#
and p.obj# = dep.p_obj#
and d.remoteowner is null
and p.owner# = &user_id
and d.owner# = u.user#
and p.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
and ((u.type# != 2
and bitand(u.spare1, 16) = 0
and u.user# != p.owner#)
or
(d.type# not in (4,5,7,8,9,10,11,12,13,14,22,87)
)
)
/
Output from the script
OWNER# NAME NAME NAMESPACE STIME
---------- ------------------------------ ------------------------------ ---------- ---------
258 TEST_CODEB CB 2 05-MAY-10
When we ran this on one of our complex databases, we had 35 objects listed in just one schema which were preventing us enabling Editions (a test system I must point out… we are not ready for live yet). Most were the simple case of needing to also enable editions on other dependent schemas, but we also had problems with user written functional indexes, many of our Advanced Queues. The AQ and functional indexed had to be moved out to non editionable schemas.
summary
I hope I have shown the value of tracing Oracle commands, how you might be able to use the information. I also hope you can see you need to be careful using “alter user x enable editions force” and the internal sql statement can help identify objects which cause issues.
