π§ 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
π§± 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
π 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
π 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
π 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
π Example Query β Index Details:
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
π Indexed Columns:
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE index_name = 'EMP_IDX1';
π 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)
π Example Query β Trigger Info:
SELECT trigger_name, triggering_event, table_name, status, description
FROM user_triggers
WHERE table_name = 'EMPLOYEES';
π 6. Views
View Description
USER_VIEWS Views owned by the user
ALL_VIEWS Views accessible to user
DBA_VIEWS All views in the database
π οΈ 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.)
π 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';
π 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
Top comments (0)