CODEWITHSUNDEEP
sqloracle-databaseviewsql-grant
Martin08
Martin08

Reputation: 21450

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

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

Upvotes: 2

Views: 8929

Answers (3)

Colin Nicholls
Colin Nicholls

Reputation: 329

Assuming you have permissions, you could try:

ALTER SESSION SET CURRENT_SCHEMA=USERB;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332591

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

Upvotes: 3

Related Questions