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

- 13,286
- 2
- 28
- 32
-
2the "most complete answer" apart from its use of `%_OBJECTS` instead of `%_TABLES`. – APC Feb 12 '10 at 07:14
-
I remember in 9i that views would be listed in %_TABLES -- so, for example, trying to automate emptying a schema would end up with statements like DROP TABLE REALLY_A_VIEW CASCADE CONSTRAINTS throwing errors. So you'd either have to remove the views with MINUS / NOT IN / NOT EXISTS or go agains %_OBJECTS. Plus, going against %_OBJECTS leaves a tantalizing hint of what else might be in there! – Adam Musch Feb 12 '10 at 16:45
-
1There is absolutely no need for the `DISTINCT` in the query. `owner, object_name` is unique in `ALL_OBJECTS` – Aug 20 '13 at 09:17
-
2Those queries don't need distinct, that's true; however, `owner, object_name` is not unique by any means in dba_objects; Package Bodies and Packages both appear in that view, and Tables and Indexes are in different namespaces. – Adam Musch Aug 20 '13 at 20:59
-
The last query won't work as is in XE 11.2. There is no 'owner' column in 'user_objects' it seems. – Rafael Chaves Jan 22 '15 at 12:44
SELECT table_name from all_tables where owner = 'YOURSCHEMA';

- 43,810
- 29
- 138
- 169
-
2This will only show *all* the tables in YOURSCHEMA if run by YOURSCHEMA or run by a user with the privileges mentioned by Adam Musch. Otherwise it just shows the tables in YOURSCHEMA to which we have been granted privileges. – APC Feb 12 '10 at 07:17
You can query USER_TABLES
select TABLE_NAME from user_tables

- 21,321
- 22
- 95
- 134
-
5That's all the tables in YOUR schema, not all the tables in A schema. Also, the *_TABLES data dictionary views (DBA_TABLES, ALL_TABLES, USER_TABLES) include views. – Adam Musch Feb 11 '10 at 21:04
-
replace "include views" with "can include views in seme versions of Oracle." – Adam Musch Feb 11 '10 at 21:14
-
@Adam Musch Tested using Oracle 10g R2, it didn't return views. – Sathyajith Bhat Feb 11 '10 at 23:27
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%');

- 146
- 2
- 7
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;

- 30,033
- 48
- 152
- 225

- 65
- 1
- 1
select * from cat;
it will show all tables in your schema cat synonym of user_catalog

- 607
- 7
- 10
Try this, replace ? with your schema name
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA =?
AND TABLE_TYPE = 'BASE TABLE'

- 132,095
- 25
- 206
- 225
-
3This is more database agnostic, and so I think this type of solution is better over all the alternatives. I think INFORMATION_SCHEMA works on just about every major database I've seen. Although some differ in what information you can get out, at least it's a consistent place to look. However from doing a quick internet search, it appears Oracle is just about the only database to not support Information_Schema, even though it's part of the SQL-92 standard. – Kibbee Feb 11 '10 at 21:19
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

- 176,835
- 32
- 241
- 292
select TABLE_NAME from user_tables;
Above query will give you the names of all tables present in that user;

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

- 39
- 1
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'

- 734
- 1
- 10
- 24
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

- 53,067
- 18
- 70
- 114
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

- 1
- 1