How to List All Tables in Oracle

In this post, we will talk about three different views that can make Oracle show tables within defined scope, they are:

1. Tables that You Own
2. Tables that You Can Use
3. Tables for Whole Database

Later on, we will talk about some useful concepts that are really helpful for you to understand the entire Oracle database better:

4. Scope of Views on Database Tables
5. Table Statistics

Tables that You Own

To make Oracle show tables that belong to you in the entire Oracle database in a list, you can query USER_TABLES which is an Oracle data dictionary view used to list tables information of all yours.

For example, we'd like to know all tables that belong to us, we can list all items by logging on as a whatever user and selecting TABLE_NAME from USER_TABLES.

SQL> conn hr/hr;
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
LOCATIONS
...

The listed tables that you got are all yours in the entire Oracle database, which means that they are all in your schema domain. As you have already known, one user can only has one schema, no more no less, so different users have definitely different lists of USER_TABLES which reflects their own schema tables, also called segments, no items come from other user.

Now, what is segments and what is schema object? I think you have to know the differences between segments and schema objects to clarify some concepts.

Tables that You Can Use

If you have at least SELECT privilege on other users' tables, you can display all of them including yours by querying ALL_TABLES. It's also an Oracle data dictionary view which contains the entire tables that you can use no matter who owns them.

SQL> select owner, table_name from all_tables;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            DUAL
...

For tables owned by other users, you have to be granted before using them and then the usable tables will be listed in your ALL_TABLES scope of view. To know the privilege status on a specific table, there're several ways that can check if the table has been ever granted to you.

On the other hand, if you accidentally use a table that has never granted to you, you will meet the most famous error: ORA-00942: table or view does not exist, there're some solutions for you.

Tables for Whole Database

Let's go deeper. For all tables no matter who owns them in the entire Oracle database, you can query the absolute list of DBA_TABLES if you have DBA privilege. This is an exhausted and complete list of tables in the entire Oracle database. Once again, DBA_TABLES is an Oracle data dictionary view.

To prove that DBA_TABLES can only be queried by users who have DBA privilege, we can make a test.

SQL> select owner, table_name from dba_tables;
select owner, table_name from dba_tables
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

What? We got ORA-00942: table or view does not exist eventually. The error means that current user does not have enough privilege to show the complete list of tables in the whole database. So we use SYS instead.

SQL> conn / as sysdba
Connected.
SQL> select owner, table_name from dba_tables;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            TYPE_MISC$
...

Now, we can see the absolute and complete list of our whole database. That is to say, no other list is ultimate like this one.

Scope of Views on Database Tables

Generally speaking, USER_TABLES, ALL_TABLES and DBA_TABLES are all Oracle data dictionary views which represent different points of view on tables according to your privileges.

In terms of visible scope of views on whole database tables, the exhausted and complete list DBA_TABLES is the largest, which is larger than usable scope ALL_TABLES, and ALL_TABLES is larger than or equal to respective owner's scope USER_TABLES.

Table Statistics

Simply listing all table names in different scope of views may be too dull to be informative. Sometimes, you would like to know which users owns the most tables, and furthermore, what tables take most space of the entire database system. In such moment, we need an approach to know the database better.

To make yourself have a overall picture about the entire database after listing all tables in Oracle, you can count all tables for each user totally. Through aggregation function COUNT and aggregation clause GROUP BY, we can show the statistics by querying a complete and exhausted dictionary view, DBA_TABLES.

SQL> set pagesize 1000;
SQL> select owner, count(table_name) as num_of_tabs from dba_tables group by owner order by 1;

OWNER                          NUM_OF_TABS
------------------------------ -----------
APEX_030200                            360
APPQOSSYS                                4
...
23 rows selected.

In practice, the table statistics provide DBA an overall glance at the whole database. It's pretty helpful when you take over a database from other DBA's hands.

Furthermore, you may like to know the way on checking the biggest tables in size of your whole database.

Grant All Tables Owned by Others

Here comes another advanced topic. How to get the privileges of using tables that belong to others at once? Is there any quick command to do so? If you want to query all of other user's tables, ask your DBA to grant SELECT privileges on those tables at object level or system level, otherwise you will get ORA-00942: table or view does not exist.