Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

How do I list all tables in a schema in Oracle SQL?

1 Answer

0 votes
by (40.7k points)

To check all tables in another schema, you should have one or more of the following system privileges:

SELECT ANY DICTIONARY

(SELECT | INSERT | UPDATE | DELETE) ANY TABLE

or the big-hammer, the DBA role.

With any of those privileges, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 

  FROM DBA_OBJECTS

 WHERE OBJECT_TYPE = 'TABLE'

   AND OWNER = '[some other schema]'

But, without those system privileges, you can just see the tables you have been granted some level of access to, whether directly or through a role like this:

SELECT DISTINCT OWNER, OBJECT_NAME 

  FROM ALL_OBJECTS

 WHERE OBJECT_TYPE = 'TABLE'

   AND OWNER = '[some other schema]'

Ultimately, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):

SELECT DISTINCT OBJECT_NAME 

  FROM USER_OBJECTS

 WHERE OBJECT_TYPE = 'TABLE'

Related questions

0 votes
2 answers
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...