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

Question

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

Answer 1

You can query USER_TABLES

select TABLE_NAME from user_tables

Answer 2

Try this, replace ? with your schema name

select TABLE_NAME from  INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA =?
  AND TABLE_TYPE = 'BASE TABLE'

Answer 3

Look at my simple utility to show some info about db schema. It is based on: Reverse Engineering a Data Model Using the Oracle Data Dictionary

Answer 4

SELECT table_name  from all_tables where owner = 'YOURSCHEMA';

Answer 5

If you are accessing Oracle with JDBC (Java) you can use DatabaseMetadata class. If you are accessing Oracle with ADO.NET you can use a similar approach.

If you are accessing Oracle with ODBC, you can use SQLTables function.

Otherwise, if you just need the information in SQLPlus or similar Oracle client, one of the queries already mentioned will do. For instance:

select TABLE_NAME from user_tables

Answer 6

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 OWNER, OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

Answer 7

http://www.psoug.org/reference/library.html

A good source for oracle commands