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

oracle

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

Best Answer

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

Related Question