How can I list Oracle editions granted?

Question

In using Oracle Edition Based redefinition, I am receiving the error:

ORA-38802: edition does not exist

when issuing the call:

DBMS_SESSION.SET_EDITION_DEFERRED("my_edition")

I know the edition exists because I can query the DBA_EDITIONS view and I can see the edition. The problem must be that the user must not have been granted use privs on the edition. I'd like to be able to verify which users have been granted use privileges on which editions. How is this done?

Answer 1

DBA_TAB_PRIVS is what you're looking for. A demo is the best way to illustrate.

Create the edition.

SQL> create edition wibble;

Edition created.

SQL>

No grants yet:

SQL> select table_name as edition_name, grantor, grantee
  2  from dba_tab_privs
  3  where privilege='USE'
  4  and table_name='WIBBLE';

no rows selected

SQL>

Edition created, shouldn't be able to use it.

SQL> conn phil/phil
Connected.
SQL> alter session set edition = wibble;
ERROR:
ORA-38802: edition does not exist

Grant:

SQL> conn / as sysdba
Connected.
SQL> grant use on edition wibble to phil;

Grant succeeded.

SQL> 

Should be able to use it now:

SQL> conn phil/phil
Connected.
SQL> alter session set edition=wibble;

Session altered.

SQL>

Grants are now visible:

SQL> select table_name as edition_name, grantor, grantee
  from dba_tab_privs
  where privilege='USE';

EDITION_NAME                   GRANTOR GRANTEE
------------------------------ ------- -------
ORA$BASE                       SYS     PUBLIC
WIBBLE                         SYS     PHIL

SQL>