ADF : AM throwing ora-28009 error

Hi All,

This blog is though mainly for fusion middleware and fusion application technology stack, from time to time I'll share my quick Oracle DB fixes which came handy while debugging issues encountered while working with technologies mentioned before.

While running my Application module (which is based on few Entity objects and updateable View Objects) , I faced the following error :

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Please note :

1. My underline DB is Oracle XE
2. My default schema is sys 
3. My Jdeveloper DB connection is sys/sys ; role : sysdba ; Hostname : Localhost ; SID: XE ; JDBC Port : 1521 , it gives SUCCESS while testing the connection.

The fix to my problem was to bypass the SYSDBA or SYSOPER checking step, and we can do it this way:

1. Log in to sqlplus as sys  i.e use the following command in sql prompt
   conn / as sysdba
2. Check for parameter named O7_DICTIONARY_ACCESSIBILITY
   i.e use the following command in sql prompt
   show parameter O7_DICTIONARY_ACCESSIBILITY
   
   It will give you result as shown below
   O7_DICTIONARY_ACCESSIBILITY boolean FALSE
    
3. Change this parameter using
   alter system set O7_dictionary_accessibility=TRUE scope=spfile;
4. It is a static parameter so need to bounce the database . Restart your database .
5. Now connect as SYS without SYSDBA privilege.
   conn sys/<your SYS password>
6. Now in your jdeveloper, refresh the DB connection and rebuild the application. Run it, now you should be able to run the AM.
   
   
   P.S : Setting O7_DICTIONARY_ACCESSIBILITY to TRUE is vulnerable and not a reccomended way. It will allow anyone with 'select any table' priviledge to select tables like user$, obj$, seq$ etc.