1

I want to enable Edition Based Redefinition for a user. Executing this command gives an ORA-33819 exception:

ALTER USER user ENABLE EDITIONS;

I know there's an option to enable it with the parameter Force

ALTER USER user ENABLE EDITIONS FORCE;

but as this operation is retroactive and irreversible, I would rather see beforehand why it failed. Does anyone know how to get a list of problems before altering the user?

Working on an Oracle 12.1.0.1.0

0

You can query something like this:

select o.owner, o.object_name, o.object_type, edition_name, editionable from dba_dependencies d, dba_objects o 
where o.owner = d.referenced_owner
and o.object_name = d.referenced_name
and d.owner = 'USER'
and o.editionable = 'Y'
and o.edition_name is NULL;

This is will list the objects referenced by user 'USER', that are editionable but dont have any editions - thus preventing you from enabling EBR for 'USER'.

  • That didn't work. With your view I have a list of all the objects that are editionable, but not the ones that will give problems. As a test, I created a user, and then a table and package for that user. When executing your query I had a list of 2 objects. But enabling EBR for this user worked like a charm. – rvheddeg Sep 11 '13 at 7:37
0

1) First you need to be sure that there are no more evolved ADT (Abstract Data Types):

select t.*
from DBA_TYPE_VERSIONS t
where Owner = 'USER'
  and t.version# > 1;

You have to reset all this types before you can enable Edition Based Redefinition for a user.

 ALTER TYPE *TYPE_NAME* RESET;

Restriction on RESET: You cannot specify RESET if the type has any table dependents (direct or indirect)

Link: ALTER TYPE Statement

2) If you have tables which are based on XML Schema's you'll have to move the definition to another schema and recreate the table based on that definition. It's a bit strange though, I had expecting that those types would be generated "NONEDITIONABLE" by default?

 select *
 from USER_XML_SCHEMAS;

Link: XML Schema Storage

3) If you are using Advanced Queueing, drop your queue's, and types and recreate the types with the NONEDITIONABLE property:

select * --> Look for "Object_Type" = Type_Name
from User_Queue_Tables;

drop queue ...;
drop type *TYPE_NAME*;

create NONEDITIONABLE type *TYPE_NAME* as ...;
create queue ...;

So, that did it for me

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.