15.1 Data Dictionary Structure & View Prefixes

15.1 Data Dictionary Structure & View Prefixes

In Oracle Database, the Data Dictionary is the central, read-only repository of metadata that describes every logical and physical structure within the database. It maintains real-time definitions of tables, columns, constraints, indexes, views, sequences, users, privileges, auditing rules, and storage allocations.

For the Oracle Database SQL (1Z0-071) examination, understanding how the data dictionary is organized, how Oracle maintains it, and how to query its three standard view prefixes (USER_*, ALL_*, DBA_*) and dynamic views (V$) is essential for effective schema inspection, troubleshooting, and administrative auditing.

---

Data Dictionary Architecture & Lifecycle

The Oracle Data Dictionary consists of two core components:

1. Base Tables: Internal tables (such as tab$, col$, obj$, con$, user$, seg$) created during database creation under the SYS schema and physically stored in the SYSTEM tablespace.
2. Data Dictionary Views: User-accessible views and public synonyms built on top of the base tables to present complex internal pointers and surrogate keys as readable, normalized metadata.

+-------------------------------------------------------------------------+
|                    DATA DICTIONARY ARCHITECTURE                         |
+-------------------------------------------------------------------------+
|                                                                         |
|   [ Application / Developer SQL ]                                       |
|                  │                                                      |
|                  │ (Queries Metadata)                                   |
|                  ▼                                                      |
|   ┌─────────────────────────────────────────────────────────────┐       |
|   │                  DATA DICTIONARY VIEWS                      │       |
|   │  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐  │       |
|   │  │    USER_*    │    │    ALL_*     │    │    DBA_*     │  │       |
|   │  │ (Own Schema) │    │ (Accessible) │    │ (Whole DB)   │  │       |
|   │  └──────────────┘    └──────────────┘    └──────────────┘  │       |
|   └──────────────────────────────┬──────────────────────────────┘       |
|                                  │ (Decodes internal joins)             |
|                                  ▼                                      |
|   ┌─────────────────────────────────────────────────────────────┐       |
|   │                   SYS BASE TABLES (tab$, col$, obj$)        │       |
|   │   - Owned exclusively by SYS in SYSTEM tablespace           │       |
|   │   - Strictly read-only for users & DBAs                     │       |
|   │   - Automatically maintained by Oracle during DDL           │       |
|   └──────────────────────────────▲──────────────────────────────┘       |
|                                  │ (Automatic Internal DML)             |
|   [ DDL Statements: CREATE / ALTER / DROP / GRANT / TRUNCATE ]          |
|                                                                         |
+-------------------------------------------------------------------------+

Automatic Maintenance via DDL

Whenever a user executes a Data Definition Language (DDL) command—such as CREATE TABLE, ALTER TABLE, DROP SEQUENCE, or GRANT—the Oracle database engine automatically performs internal updates against the underlying base tables.

-- When this DDL executes:
CREATE TABLE hr.projects (
    project_id   NUMBER(6) PRIMARY KEY,
    title        VARCHAR2(100) NOT NULL
);

-- Oracle's internal engine automatically:
-- 1. Inserts a row for 'PROJECTS' into sys.obj$
-- 2. Inserts rows for 'PROJECT_ID' and 'TITLE' into sys.col$
-- 3. Inserts constraint definitions into sys.con$ and sys.cdef$
-- 4. Allocates initial data segment extents and updates sys.seg$
-- 5. Executes an implicit COMMIT

Critical Exam Rule: Users and administrators should never issue direct INSERT, UPDATE, or DELETE statements against SYS base tables. Manual modification of base tables corrupts data dictionary integrity and can cause irreversible database instance crashes. Metadata must be manipulated exclusively via DDL commands.

---

The Three Standard View Prefixes

To provide secure, tailored visibility into metadata, Oracle groups static dictionary views into three distinct tiers using standardized prefixes:

                         DATA DICTIONARY VIEW TIERS
                                     │
        ┌────────────────────────────┼────────────────────────────┐
        │                            │                            │
   USER_VIEWS                    ALL_VIEWS                    DBA_VIEWS
   - Objects owned by           - Objects accessible by      - Every object across
     current user schema          current user                 entire database
   - No OWNER column            - Includes OWNER column      - Includes OWNER column
   - Lowest privileges          - Ownership + Grants         - Restricted (DBA role)

Detailed Comparison of View Prefixes

Demonstration of Prefix Behavior

Assume a developer is logged in as user SCOTT:

-- 1. Querying USER_TABLES (Shows only tables owned by SCOTT):
SELECT table_name 
FROM user_tables;
-- Returns: EMP, DEPT, BONUS, SALGRADE
-- Note: There is NO 'owner' column in USER_TABLES!

-- 2. Querying ALL_TABLES (Shows tables owned by SCOTT + granted to SCOTT):
SELECT owner, table_name 
FROM all_tables
ORDER BY owner, table_name;
-- Returns SCOTT.EMP, SCOTT.DEPT, plus HR.EMPLOYEES, OE.CUSTOMERS (if granted)

-- 3. Querying DBA_TABLES (Shows all tables across all schemas in the database):
SELECT owner, table_name 
FROM dba_tables
WHERE owner IN ('SYS', 'SYSTEM', 'HR', 'SCOTT');
-- Fails with ORA-00942 if SCOTT lacks DBA or SELECT ANY DICTIONARY privilege

Exam Trap: USER_* views do not contain an OWNER column. Attempting to execute SELECT owner, table_name FROM user_tables; will result in ORA-00904: "OWNER": invalid identifier. In contrast, ALL_* and DBA_* views always include OWNER.

---

Dynamic Performance Views (V$ and GV$)

In addition to the static metadata views describing persistent database schema structures, Oracle provides Dynamic Performance Views (often called V$ views) to monitor real-time database operation, instance memory structures, and performance telemetry.

+-------------------------------------------------------------------------+
|               STATIC METADATA VS. DYNAMIC PERFORMANCE VIEWS             |
+-------------------------------------------------------------------------+
| Characteristic        | Static Views (USER/ALL/DBA) | Dynamic Views (V$)|
| :-------------------- | :-------------------------- | :-----------------|
| Source of Data        | Database files on disk      | Instance SGA memory|
|                       | (SYSTEM tablespace)         | & Control Files   |
| Persistence           | Permanent metadata          | Transient (reset  |
|                       | across database restarts    | on instance restart)|
| Primary Content       | Schema definitions          | Active sessions, locks,|
|                       | (tables, columns, FKs)      | memory caches, I/O|
| Maintained By         | DDL statements              | Background processes|
| Base Structures       | SYS base tables (tab$, obj$) | Virtual X$ structures|
+-------------------------------------------------------------------------+

V$ vs. GV$ Views

• V$ Views: Reflect memory and session telemetry for the local database instance.
• GV$ (Global V$) Views: Reflect real-time telemetry across all instances in an Oracle Real Application Clusters (RAC) environment. GV$ views contain an additional column named INST_ID (Instance Identifier) to indicate which RAC node produced the metrics.

-- View current active sessions in the local instance:
SELECT sid, serial#, username, status, program
FROM v$session
WHERE status = 'ACTIVE';

-- View database parameter configuration:
SELECT name, value, description
FROM v$parameter
WHERE name = 'db_block_size';

---

Discovering Data Dictionary Views (DICTIONARY & DICT_COLUMNS)

Because the Oracle Data Dictionary contains hundreds of views, Oracle provides built-in discovery catalogs so developers can search for appropriate metadata views without memorizing every name.

                          DICTIONARY DISCOVERY VIEWS
                                      │
          ┌───────────────────────────┴───────────────────────────┐
          │                                                       │
     DICTIONARY (DICT)                                      DICT_COLUMNS
     - Lists all dictionary views                            - Lists all columns in every
     - Columns: TABLE_NAME, COMMENTS                           dictionary view
                                                             - Columns: TABLE_NAME, 
                                                               COLUMN_NAME, COMMENTS

1. The DICTIONARY (or DICT) View

The DICTIONARY catalog (available via the shorthand synonym DICT) lists the names and descriptions of all data dictionary views accessible to the current user:

-- Find all dictionary views related to constraints:
SELECT table_name, comments
FROM dictionary
WHERE table_name LIKE '%CONSTRAINT%'
ORDER BY table_name;

-- Search using the DICT synonym for views documenting privileges:
SELECT table_name, comments
FROM dict
WHERE UPPER(comments) LIKE '%PRIVILEGE%'
ORDER BY table_name;

2. The DICT_COLUMNS View

When you know the name of a dictionary view but need to understand the purpose of specific columns—or when you want to find which dictionary views contain a specific column—query DICT_COLUMNS:

-- Inspect column descriptions for USER_TABLES:
SELECT column_name, comments
FROM dict_columns
WHERE table_name = 'USER_TABLES'
ORDER BY column_name;

-- Find which dictionary views contain the column 'TABLESPACE_NAME':
SELECT table_name, comments
FROM dict_columns
WHERE column_name = 'TABLESPACE_NAME'
  AND table_name LIKE 'USER_%'
ORDER BY table_name;

Discovery Best Practices Matrix