Data Dictionary View

How to List All Tables in Oracle

USER_TABLES vs ALL_TABLES vs DBA_TABLES

List All Tables You Own

To list all tables that belong to you, you can query USER_TABLES which is an Oracle data dictionary view used to look up table information of yours.

The Scope of USER_TABLES
The Scope of USER_TABLES
SQL> conn hr/hr;
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
LOCATIONS
COUNTRIES
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
REGIONS

7 rows selected.

The listed tables are all yours in Oracle database, which means they are in your schema domain. One user, one schema.

List All Tables You Can Use

If you have at least select privilege on other users’ tables, you can display them in ALL_TABLES. It’s also an Oracle data dictionary view.

The Scope of ALL_TABLES
The Scope of ALL_TABLES
SQL> select owner, table_name from all_tables;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            DUAL
SYS                            SYSTEM_PRIVILEGE_MAP
SYS                            TABLE_PRIVILEGE_MAP
SYS                            STMT_AUDIT_OPTION_MAP
SYS                            AUDIT_ACTIONS
SYS                            WRR$_REPLAY_CALL_FILTER
SYS                            HS_BULKLOAD_VIEW_OBJ
SYS                            HS$_PARALLEL_METADATA
SYS                            HS_PARTITION_COL_NAME
SYS                            HS_PARTITION_COL_TYPE
SYSTEM                         HELP

List All Tables in Database

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

The Scope of DBA_TABLES
The Scope of DBA_TABLES
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

Which means, HR does not have dba privilege to list all tables in Oracle 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$
SYS                            ATTRCOL$
SYS                            ASSEMBLY$
SYS                            LIBRARY$
SYS                            VIEWTRCOL$
SYS                            ICOLDEP$
SYS                            OPQTYPE$
SYS                            REFCON$
SYS                            NTAB$
SYS                            SUBCOLTYPE$
SYS                            COLTYPE$

To make yourself have a picture about your database after listing all tables in Oracle, you can count tables for each user through aggregation function COUNT and aggregation syntax GROUP BY.

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
CTXSYS                                  50
DBSNMP                                  20
EXFSYS                                  47
FLOWS_FILES                              1
HR                                       7
IX                                      17
MDSYS                                  129
OE                                      11
OLAPSYS                                126
ORDDATA                                 73
ORDSYS                                   5
OUTLN                                    3
OWBSYS                                   1
PM                                       2
SCOTT                                    4
SH                                      17
SYS                                   1015
SYSMAN                                 728
SYSTEM                                 167
WMSYS                                   44
XDB                                     32

23 rows selected.

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 database tables, the exhausted and complete list DBA_TABLES is the largest, which is larger than ALL_TABLES, and ALL_TABLES is larger than or equal to USER_TABLES.

Scope of Views on DBA_TABLES, ALL_TABLES and USER_TABLES
Scope of Views on DBA_TABLES, ALL_TABLES and USER_TABLES

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.

Segment

A segment in 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 more information. Since segments contains real data, they usually reside in their owner’s default tablespace and consequently take some disk space.

Schema Objects

A schema in 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, program 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 list all tables, because they are the most important schema object in Oracle database.

Leave a Reply

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