how to select a Oracle schema-specific view from another schema

Question

Suppose I'm logged in as USERA, I want to access all the user_* views of the USERB schema, such as user_tables, user_tab_columns. How can I do this? Thanks

Answer 1

All the USER_* tables have analogues with the ALL_* and DBA_* prefix. USER_TABLES has information about all the tables you own. ALL_TABLES has information about all the tables you have access to. DBA_TABLES has information about all the tables in your database.

If you want to see information about UserB's tables

SELECT *
  FROM all_tables
 WHERE owner = 'USERB';

or

SELECT *
  FROM dba_tables
 WHERE owner = 'USERB';

The former will work if you have SELECT access on User B's tables. The latter will work if your DBA has given you access to the DBA_TABLES view. That is normally done by granting the SELECT ANY DICTIONARY privilege (or the SELECT_CATALOG_ROLE in prior version) though the DBA can grant access to individual DBA_* views.

Answer 2

• USER_% views give what you own, that is what's inside your schema.
• ALL_% views give what you have access to.

So what you really should use is ALL_TABLES/etc, and grant appropriate access to USERB objects.

Answer 3

Assuming you have permissions, you could try:

ALTER SESSION SET CURRENT_SCHEMA=USERB;