15.1 Data Dictionary Structure & View Prefixes

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

15.1 Data Dictionary Structure & View Prefixes

Key Takeaways Explain This Topic AI

The Oracle Data Dictionary is a read-only repository of underlying base tables (owned by SYS in the SYSTEM tablespace) that the database engine automatically updates during DDL operations.

End-users and database administrators access metadata exclusively through three standardized view tiers: USER_* (current schema), ALL_* (accessible objects), and DBA_* (all objects across the database instance).

USER_* views omit the OWNER column because the querying user is implicitly the owner, whereas ALL_* and DBA_* views include the OWNER column to distinguish object provenance.

Dynamic performance views (prefixed with V$ for single-instance and GV$ for cluster/RAC environments) reflect transient memory structures from the SGA and control files, unlike persistent static dictionary views.

The DICTIONARY (or DICT) catalog view and DICT_COLUMNS view serve as searchable indexes for discovering view names and column definitions using standard SQL LIKE queries.

Last updated: August 2026

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:

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.

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

Feature USER_* Views ALL_* Views DBA_* Views

Scope of Objects Objects created in and owned by the current user's schema . Objects owned by the current user PLUS objects granted to the user (via explicit privilege, role, or PUBLIC ). Every object in the database instance, regardless of owner or access grants.

OWNER Column? NO. Omitted because the owner is implicitly the connected user ( USER ). YES. Required to identify which schema owns the accessible object. YES. Required to identify which schema owns the object across the database.

Required Privileges Available to all users by default ( PUBLIC access). Available to all users by default ( PUBLIC access). Requires DBA role or SELECT ANY DICTIONARY system privilege.

Typical Use Case Application developers inspecting their own schema objects. Developers inspecting tables they have permission to query, join, or reference. Database Administrators auditing enterprise security, capacity, and system topology.

Row Count Relationship Rows ( USER ) ≤ Rows ( ALL ) \text{Rows}(\text{USER}) \le \text{Rows}(\text{ALL}) Rows ( USER ) ≤ Rows ( ALL ) Rows ( ALL ) ≤ Rows ( DBA ) \text{Rows}(\text{ALL}) \le \text{Rows}(\text{DBA}) Rows ( ALL ) ≤ Rows ( DBA ) Rows ( DBA ) ≥ Rows ( ALL ) ≥ Rows ( USER ) \text{Rows}(\text{DBA}) \ge \text{Rows}(\text{ALL}) \ge \text{Rows}(\text{USER}) Rows ( DBA ) ≥ Rows ( ALL ) ≥ Rows ( USER )

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

Discovery Objective Recommended SQL Query
Find views related to indexes SELECT table_name, comments FROM dict WHERE table_name LIKE '%INDEX%';
Find views related to storage quotas SELECT table_name, comments FROM dict WHERE table_name LIKE '%TS_QUOTA%';

Explain columns in USER_TAB_COLUMNS SELECT column_name, comments FROM dict_columns WHERE table_name = 'USER_TAB_COLUMNS';

Locate views with LAST_DDL_TIME SELECT table_name, comments FROM dict_columns WHERE column_name = 'LAST_DDL_TIME';