SQL & PL/SQL

Greetings All,

I have an issue where an account(user/schema owner) on the production instance cannot see several (perhaps all) dba_tables/views, specifically dba_segments, dba_objects, dba_recyclebin. Both platforms are 11gR2.

The same account/user on the development platform CAN however see these tables/views. This user was created and granted privileges/roles from the very same SQL script on both platforms.

Another thing that I experienced/noticed is that while using Oracle’s SQL Developer, this user/account on the production platform is able to reference these tables from the SQL editor window, but when I try to compile a procedure with the exact same SQL select statement the compile fails with ORA-00942: table or view does not exist.

I have tried referencing by fully qualifying as sys.dba_segments. Public synonyms do exist on both platforms.

I have even tried explicitly granting select on DBA_SEGMENTS (and sys.dba_segments) to this user with no effect.

I have compared the production privileges/grants/roles/synonyms to that of the development platform for this user with DBA_ROLE_PRIVS, DBA_SYS_PRIVS, ROLE_TAB_PRIVS, USER_ROLE_PRIVS, SESSION_PRIVS, and several others. All were identical to each other. This user has been granted the DBA role on both platforms.

What else could I look at or do to identify why this user cannot reference these views/tables on the production platform but can on the development.

Thanks for your time,

Bob