Data Dictionary (or Data Dictionary Views)
✅ What is the Data Dictionary?
The Data Dictionary is a set of read-only tables and views that Oracle maintains to store metadata — i.e., data about the data. It includes information like:
Structure of database objects (tables, columns, constraints, etc.)
User permissions and roles
Storage and performance statistics
Dependencies between objects
Triggers, indexes, sequences, etc.
📂 Categories within the Data Dictionary:
Category Examples Purpose
Object Definitions ALL_TABLES, ALL_TAB_COLUMNS Info about tables, columns, datatypes
Constraints ALL_CONSTRAINTS, ALL_CONS_COLUMNS Info about PKs, FKs, checks, etc.
Indexes ALL_INDEXES, ALL_IND_COLUMNS Index structure and column mappings
User/Security Info ALL_USERS, DBA_USERS, USER_ROLE_PRIVS Info about users and privileges
Storage/Performance ALL_TAB_STATISTICS, ALL_HISTOGRAMS Info on table stats, optimizer data
Dependencies ALL_DEPENDENCIES, DBA_DEPENDENCIES Which objects depend on which others
Synonyms/Views ALL_VIEWS, ALL_SYNONYMS Info about synonyms and view structures
Oracle provides a rich set of data dictionary views (tables) that start with prefixes like ALL_, USER_, and DBA_. These are extremely helpful for querying metadata (i.e., information about the database itself — like tables, columns, constraints, indexes, etc.).
🔹 Key Prefixes:
Prefix Description
USER_ Shows objects owned by the current user.
ALL_ Shows objects accessible to the current user (owned or granted access).
DBA_ Shows all objects in the database (requires DBA privilege).
🔍 Useful Data Dictionary Views:
📌 Table & Column Info:
View Name Description
ALL_TABLES Lists tables accessible to the user.
ALL_TAB_COLUMNS Lists columns in those tables.
ALL_TAB_COMMENTS Comments on tables and views.
ALL_COL_COMMENTS Comments on columns.
🔐 Constraints:
View Name Description
ALL_CONSTRAINTS Details about all constraints (PK, FK, UK, CK) on tables.
ALL_CONS_COLUMNS Maps constraints to the columns they apply to.
🗝️ Keys & Indexes:
View Name Description
ALL_INDEXES Lists indexes defined on tables.
ALL_IND_COLUMNS Lists columns that are part of those indexes.
ALL_UNIQUE_KEYS (Older versions only) Lists unique keys. Now handled via ALL_CONSTRAINTS.
🔄 Triggers & Dependencies:
View Name Description
ALL_TRIGGERS Lists all triggers accessible to the user.
ALL_DEPENDENCIES Shows dependency between objects (e.g., view depending on table).
👁️ Views & Synonyms:
View Name Description
ALL_VIEWS Details of views accessible to the user.
ALL_SYNONYMS Synonyms accessible to the user.
👤 User & Privileges:
View Name Description
ALL_USERS Lists all users.
ALL_TAB_PRIVS Shows table-level privileges.
SESSION_PRIVS Privileges for the current session.
Top comments (0)