DEV Community

mrcaption49
mrcaption49

Posted on

Data Dictionary Views in Oracle Database

#oracle #data #database #datastructures

🧠 What Is a Data Dictionary?

The Data Dictionary in Oracle is a centralized repository of metadata that describes the structure, permissions, constraints, and relationships of all objects in the database. Oracle exposes this via read-only views.

📂 Categorization by Scope 

Prefix          Scope of Objects

USER_           Objects owned by the current user
ALL_            Objects accessible to the user
DBA_            All objects in the database (DBA access)
V$          Dynamic Performance Views (real-time DB info)
CDB_/PDB_   For multitenant databases
Enter fullscreen mode Exit fullscreen mode

🧱 Core Data Dictionary Views by Object Type

📌 1. Tables 

View                    Description

USER_TABLES         Tables owned by the current user
ALL_TABLES          All accessible tables
DBA_TABLES          All tables in the database
USER_TAB_COMMENTS   Comments on tables
USER_TAB_PRIVS          Grants on tables
Enter fullscreen mode Exit fullscreen mode

📌 2. Columns 

View                           Description

USER_TAB_COLUMNS           Columns in the user’s tables
ALL_TAB_COLUMNS                Columns in accessible tables
DBA_TAB_COLUMNS                All columns in all tables
USER_COL_COMMENTS          Comments on columns
Enter fullscreen mode Exit fullscreen mode

📌 3. Constraints 

View                    Description

USER_CONSTRAINTS    Constraints on user tables
USER_CONS_COLUMNS   Columns used in those constraints
ALL_CONSTRAINTS         Constraints accessible to user
DBA_CONSTRAINTS         All constraints in DB
Enter fullscreen mode Exit fullscreen mode

📌 4. Indexes 

View                    Description

USER_INDEXES            Indexes created by the user
ALL_INDEXES         Indexes accessible to the user
DBA_INDEXES         All indexes in the database
USER_IND_COLUMNS    Columns involved in each index
USER_IND_EXPRESSIONS    Indexed expressions (function-based)
USER_IND_STATISTICS Stats for performance analysis
Enter fullscreen mode Exit fullscreen mode

🔍 Example Query – Index Details:

SELECT index_name, table_name, uniqueness, status 
FROM user_indexes 
WHERE table_name = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

🔍 Indexed Columns:

SELECT index_name, column_name, column_position 
FROM user_ind_columns 
WHERE index_name = 'EMP_IDX1';
Enter fullscreen mode Exit fullscreen mode

📌 5. Triggers 

View                    Description

USER_TRIGGERS           Triggers owned by the user
ALL_TRIGGERS            All triggers user can access
DBA_TRIGGERS            All triggers in the database
USER_TRIGGER_COLS   Columns referenced in triggers
USER_OBJECTS            Shows status and type of triggers (STATUS column)
Enter fullscreen mode Exit fullscreen mode

🔍 Example Query – Trigger Info:

SELECT trigger_name, triggering_event, table_name, status, description
FROM user_triggers 
WHERE table_name = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

📌 6. Views 

View            Description

USER_VIEWS  Views owned by the user
ALL_VIEWS   Views accessible to user
DBA_VIEWS   All views in the database
Enter fullscreen mode Exit fullscreen mode

🛠️ Utility Views 

View                           Purpose

DICTIONARY                 Lists all data dictionary views
DICT_COLUMNS                   Columns of all dictionary views
DBA_OBJECTS / USER_OBJECTS     All types of DB objects (status, type, etc.)
Enter fullscreen mode Exit fullscreen mode

📊 Real-World Use Case: Audit Table + Trigger Setup

Goal: Automatically log changes to a table.

-- Example: Check if audit table and trigger exist
SELECT * FROM user_tables WHERE table_name = 'AUDIT_LOG';

SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

📌 Summary Table 

Object Type            Core Views

Tables                     USER_TABLES, ALL_TABLES
Columns                    USER_TAB_COLUMNS, ALL_TAB_COLUMNS
Constraints            USER_CONSTRAINTS, USER_CONS_COLUMNS
Indexes                    USER_INDEXES, USER_IND_COLUMNS, USER_IND_EXPRESSIONS
Triggers               USER_TRIGGERS, USER_TRIGGER_COLS
Views                      USER_VIEWS, ALL_VIEWS
General DICTIONARY, USER_OBJECTS
Enter fullscreen mode Exit fullscreen mode

Top comments (0)