Enabling Edition Based Redefinition failed with ora-33819. Could I query why?

Question

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

Answer 1

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'.

Answer 2

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