How to Explore the Oracle Data Dictionary

Oracle describes itself in the data dictionary: thousands of read-only views listing every table, column, index, constraint, user, and piece of code, and their state. Knowing how to find your way around it answers questions such as "which objects are invalid", "what columns does this table have", or "how many rows were there at the last statistics", in plain SQL.

Code for This Guide

The main examples are in the examples/dictionary folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

The View Families

Find the Right View with DICTIONARY

The view DICTIONARY lists the dictionary views with a description, so you can search it.

Example:

select count(*) as dictionary_views from dictionary;

select table_name, comments
from   dictionary
where  table_name like 'USER_TAB%'
order  by table_name
fetch  first 8 rows only;

Output:

   DICTIONARY_VIEWS
___________________
               5833

TABLE_NAME                    COMMENTS
_____________________________ ______________________________________________________________
USER_TABLES                   Description of the user's own relational tables
USER_TABLESPACES              Description of accessible tablespaces
USER_TABLE_ACCESS_STATS
USER_TABLE_VIRTUAL_COLUMNS    Comments on the virtual columns in tables owned by the user
USER_TAB_COLS                 Columns of user's tables, views and clusters
USER_TAB_COLS_V$
USER_TAB_COLUMNS              Columns of user's tables, views and clusters
USER_TAB_COL_STATISTICS       Columns of user's tables, views and clusters

8 rows selected.

Your Objects

USER_OBJECTS lists every object you own, with its type and status. Counting invalid objects is a quick health check after a deployment.

Example:

select object_type, count(*) as objects,
       count(case when status <> 'VALID' then 1 end) as invalid
from   user_objects
where  object_name not like 'ST0000%' and object_name not like 'SYS_%'
group  by object_type
order  by object_type;

Output:

OBJECT_TYPE       OBJECTS    INVALID
______________ __________ __________
INDEX                  27          0
SEQUENCE                7          0
TABLE                  14          0

USER_TABLES and USER_TAB_COLUMNS describe tables and their columns, including statistics such as row counts and the date they were gathered.

Example:

select table_name, num_rows, blocks, to_char(last_analyzed, 'DD-MON-YYYY') as analyzed
from   user_tables
where  table_name in ('FLIGHTS', 'BOOKINGS', 'TICKETS')
order  by table_name;

select column_id, column_name, data_type, nullable, data_default
from   user_tab_columns
where  table_name = 'AIRPORTS'
order  by column_id;

Output:

TABLE_NAME       NUM_ROWS    BLOCKS ANALYZED
_____________ ___________ _________ ______________
BOOKINGS              700         5 26-SEP-2026
FLIGHTS              2856        35 26-SEP-2026
TICKETS              1069        13 26-SEP-2026

   COLUMN_ID COLUMN_NAME     DATA_TYPE    NULLABLE    DATA_DEFAULT
____________ _______________ ____________ ___________ _______________
           1 AIRPORT_CODE    CHAR         N
           2 AIRPORT_NAME    VARCHAR2     N
           3 CITY            VARCHAR2     N
           4 COUNTRY_CODE    CHAR         N
           5 TIME_ZONE       VARCHAR2     N
           6 LATITUDE        NUMBER       N
           7 LONGITUDE       NUMBER       N
           8 ELEVATION_FT    NUMBER       Y
           9 IS_HUB          BOOLEAN      N           false

9 rows selected.

What You Can See versus What You Own

ALL_ views include objects of other schemas that you have privileges on.

Example:

select owner, count(*) as tables_i_can_see
from   all_tables
where  owner in ('NIMBUS', 'SYS', 'XDB')
group  by owner
order  by owner;

Output:

OWNER        TABLES_I_CAN_SEE
_________ ___________________
NIMBUS                     14
SYS                       142
XDB                        35

DBA_ views cover the whole database and need privileges such as SELECT_CATALOG_ROLE; this one runs as SYS.

Example:

select oracle_maintained, count(*) as users
from   dba_users
group  by oracle_maintained;

select username, account_status, default_tablespace
from   dba_users
where  username = 'NIMBUS';

Output:

ORACLE_MAINTAINED       USERS
____________________ ________
Y                          38
N                           5

USERNAME    ACCOUNT_STATUS    DEFAULT_TABLESPACE
___________ _________________ _____________________
NIMBUS      OPEN              USERS

Comments

Comments added with COMMENT ON are stored in USER_TAB_COMMENTS and USER_COL_COMMENTS, which makes the dictionary the documentation of the schema.

Example:

select table_name, comments from user_tab_comments
where  comments is not null
order  by table_name
fetch  first 5 rows only;

select column_name, comments from user_col_comments
where  table_name = 'AIRPORTS' and comments is not null;

Output:

TABLE_NAME        COMMENTS
_________________ _____________________________________________________________________________
AIRCRAFT          The fleet: one row per aircraft, identified by its registration
AIRCRAFT_TYPES    Aircraft models of the fleet
AIRPORTS          Airports that Nimbus Air serves (or plans to serve), with their time zones
BOOKINGS          Bookings (reservations); one booking has one or more tickets
COUNTRIES         Countries of the airports and customers

COLUMN_NAME    COMMENTS
______________ ________________________________________________
TIME_ZONE      Time zone region name, for example Asia/Dubai

Things to Know

• Names in the dictionary are stored in uppercase unless they were created with quotes: filter with table_name = 'AIRPORTS', not 'airports'.
• Statistics columns such as NUM_ROWS are as of the last statistics gathering, not live counts.
• The dictionary is read-only; change objects with DDL, never by updating dictionary tables.

Related Guides

• How to Add Annotations to Oracle Tables
• How to Use Pseudocolumns in Oracle

Conclusion

The data dictionary describes every object in the database through USER_, ALL_, DBA_, CDB_, and V$ views. Search DICTIONARY to find the right view, use USER_OBJECTS and USER_TAB_COLUMNS for your own schema, and ALL_ or DBA_ views to look further.