Oracle Data Dictionary Views - Complete Reference with SQL Examples

Oracle Data Dictionary Views

The Oracle data dictionary is a collection of read-only views that provide metadata about the database structure, performance, and activity. These views are essential tools for every Oracle DBA — from real-time performance troubleshooting to capacity planning and security auditing.

Performance Views (V$)

Dynamic performance views are built on top of internal memory structures (X$ tables) and provide real-time information about the running instance. They reset when the instance restarts.

Session & SQL Monitoring

View	Description
V$SESSION	All current sessions — the starting point for most performance investigations
V$SQL	Every SQL statement cached in the shared pool with execution statistics
V$SQL_PLAN	Cached execution plans for SQL statements
V$SQL_MONITOR	Real-time monitoring of long-running SQL statements
V$ACTIVE_SESSION_HISTORY	Second-by-second ASH sampling for recent performance analysis

Wait Events & Statistics

View	Description
V$SYSSTAT	System-wide cumulative statistics (buffer cache hit ratio, parse stats, I/O)
V$SYSTEM_EVENT	Aggregate wait event statistics across the instance
V$WAITSTAT	Buffer busy wait breakdown by block class
V$METRIC	Real-time performance metrics (DB time/sec, I/O throughput, transaction rate)

Time Model

View	Description
V$SESS_TIME_MODEL	Per-session DB time breakdown (CPU vs wait vs parse)
V$SYS_TIME_MODEL	System-wide DB time hierarchy and parse analysis

Locks & Transactions

View	Description
V$LOCK	All enqueue locks held and requested — blocking lock analysis
V$LOCKED_OBJECT	Which tables are locked and by which sessions
V$TRANSACTION	Active transactions with undo usage and redo generation

Memory & Resources

View	Description
V$SGA	SGA memory component sizes and resize history
V$PGASTAT	PGA memory usage, cache hit ratio, and over-allocation
V$PGA_TARGET_ADVICE	PGA sizing advisor for tuning PGA_AGGREGATE_TARGET
V$PROCESS	OS process details and per-process PGA memory
V$OSSTAT	OS-level CPU, memory, and load statistics

Configuration

View	Description
V$PARAMETER	Current instance parameter values including hidden parameters

Quick Reference: Common DBA Queries

”What’s happening right now?"

SELECT sid, serial#, username, sql_id, event,
       seconds_in_wait, blocking_session, status
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL;

"What’s the top SQL?"

SELECT sql_id, elapsed_time/1e6 as elapsed_sec,
       cpu_time/1e6 as cpu_sec, executions, buffer_gets
FROM v$sql
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

"Is anything blocked?”

SELECT l1.sid as blocker, l2.sid as waiter,
       l1.type, l2.ctime as wait_seconds
FROM v$lock l1, v$lock l2
WHERE l1.block = 1 AND l2.request > 0
  AND l1.id1 = l2.id1 AND l1.id2 = l2.id2;

ASM Views

Views for monitoring Automatic Storage Management diskgroups, disks, and files.

View	Description
V$ASM_DISK	Disk status, I/O statistics, and error counts
V$ASM_DISKGROUP	Diskgroup capacity, free space, and rebalance status
V$ASM_FILE	File sizes, redundancy, and diskgroup mapping

Schema & Admin Views

View	Description
DBA_DB_LINKS	All database links — owners, hosts, and connectivity
V$DBLINK	Active database link sessions and distributed transactions

Additional Performance Views

View	Description
V$SORT_USAGE	Sessions consuming temp tablespace — sorts, hash joins, bitmaps
NLS_SESSION_PARAMETERS	NLS session settings — date format, language, character set
DBA_HIST_ACTIVE_SESS_HISTORY	Historical ASH data for past performance analysis

Coming Soon

Additional view categories are being documented:

• Storage Views — DBA_TABLESPACES, DBA_DATA_FILES, DBA_SEGMENTS, DBA_FREE_SPACE
• Security Views — DBA_USERS, DBA_ROLES, DBA_SYS_PRIVS, DBA_TAB_PRIVS
• RAC Views — GV$ views for multi-instance monitoring
• Multitenant Views — CDB_ and PDB_ views for container databases
• Audit Views — UNIFIED_AUDIT_TRAIL, DBA_AUDIT_TRAIL