Data Dictionary View

Oracle List Tables and Show Tables

Scope of Views on DBA_TABLES, ALL_TABLES and USER_TABLES

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

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

Oracle List Tables that You Own

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

Oracle Database - USER_TABLES - The Scope of Owners
Oracle Database – USER_TABLES – The Scope of Owners

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 we got are all yours in the entire Oracle database, which means that they are all in your schema domain. In short, one user one schema. Therefore, different users have definitely different results, this is because USER_TABLES 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.

Oracle List 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.

Oracle Database - ALL_TABLES - The Scope of Usables
Oracle Database – ALL_TABLES – The Scope of Usables
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.

Oracle List Tables for Whole Database

Let’s go deeper. For entire tables no matter who owns them in 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.

Oracle Database - DBA_TABLES - The Scope of Whole Database
Oracle Database – DBA_TABLES – The Scope of Whole Database

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.

Scope of Views on DBA_TABLES, ALL_TABLES and USER_TABLES
Scope of Views on DBA_TABLES, ALL_TABLES and 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.

Segments vs Schema Objects

No only listing all tables, to better understand the nature of table, you also need to know the definitions of segment and schema object in Oracle world.

Segments

A segment in the entire Oracle database is a set of extents that contains all the data for a specific logical storage structure within a tablespace. Which means segments contain not only metadata but real data.

Typically, the types of segment are limited, you can query DBA_SEGMENTS for all types of segments. Since segments contains real data, they usually reside in their owner’s default tablespace and consequently take most of disk space. I talked about a way that can list the biggest segments in the whole database.

Schema Objects

A schema in the entire Oracle database is a collection of database objects and owned by a database user. The schema name shares the same name as the user. Schema objects are logical structures created by users.

Various types of schema object in a database can be found by querying DBA_OBJECTS. Schema objects with no data are usually stored in SYSTEM tablespace and take very little disk space. For example, programming units (stored procedures) are typical schema objects that have no data.

Differences

A segment is of course a schema object, but a schema object may not be a segment. This is the difference.

Table is a segment and also a schema object, as well as index. Which means, one table have its own data and can only belongs to one schema. This is why we want to show a list on all Oracle tables, because they are the most important objects in the entire Oracle 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.

Leave a Reply

Your email address will not be published. Required fields are marked *