Posted By: Anonymous
How do i list all tables in a schema in Oracle SQL?
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'