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

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

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

Answers

To see all tables in another schema, you need to 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, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE =  TABLE 
   AND OWNER =  [some other schema] 

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE =  TABLE 
   AND OWNER =  [some other schema] 

Lastly, 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 

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/2247310/how-do-i-list-all-tables-in-a-schema-in-oracle-sql

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils