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

How do开发者_StackOverflow中文版 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'

SELECT table_name  from all_tables where owner = 'YOURSCHEMA';

You can query USER_TABLES

select TABLE_NAME from user_tables

You can directly run the second query if you know the owner name.

--First you can select what all OWNERS there exist:

SELECT DISTINCT(owner) from SYS.ALL_TABLES;

--Then you can see the tables under by that owner:

SELECT table_name, owner from all_tables where owner like ('%XYZ%');

If you logged in as Normal User without DBA permission you may uses the following command to see your own schema's all tables and views.

select * from tab;

select * from cat;

it will show all tables in your schema cat synonym of user_catalog

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

Try this, replace ? with your schema name

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

select TABLE_NAME from user_tables;

Above query will give you the names of all tables present in that user;

select * from user_tables;

(showing all tables)

SELECT table_name, owner FROM all_tables where owner='schema_name' order by table_name

Name of the table and rows counter for all tables under OWNER schema:

SELECT table_name, num_rows counter from DBA_TABLES WHERE owner = 'OWNER'

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

If you need to get the size of the table as well, this will be handy:

select SEGMENT_NAME, PARTITION_NAME, BYTES from user_segments where SEGMENT_TYPE='TABLE' order by 1