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'
You can query USER_TABLES
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;
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
select * from user_tables;
(showing all tables)
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
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
SELECT table_name, owner FROM all_tables where owner='schema_name' order by table_name
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%');
select * from cat;
it will show all tables in your schema cat synonym of user_catalog
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'
SELECT table_name from all_tables where owner = 'YOURSCHEMA';
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;
Source: Stackoverflow.com