Skip to content
Oracle DBA Scripts & Database Utilities

Fixed Tables/Views Reference (vfixdef.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a complete reference of all Oracle fixed tables and views by:

  • Querying V$FIXED_TABLE to list all fixed objects
  • Displaying V$ views and X$ tables available in the instance
  • Providing an alphabetical listing for easy reference
  • Helping identify available system views for monitoring

Script

Section titled “Script”
rem  vfixdef.sql
rem
ttitle 'Fixed Tables/Views'
rem
col name format a30 heading 'NAME'
rem
select name
  from v$fixed_table
 order by name;

Usage

Section titled “Usage”
SQL> @vfixdef.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$FIXED_TABLE (usually available to most users)

Sample Output

Section titled “Sample Output”
Fixed Tables/Views


NAME
------------------------------
GV$ACCESS
GV$ACTIVE_SESSION_HISTORY
GV$ARCHIVE
GV$ARCHIVE_DEST
GV$ARCHIVE_DEST_STATUS
GV$ARCHIVED_LOG
GV$ASM_ALIAS
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISK_STAT
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_FILE
GV$ASM_OPERATION
GV$ASM_TEMPLATE
GV$BACKUP
GV$BACKUP_ASYNC_IO
GV$BACKUP_CORRUPTION
GV$BACKUP_DATAFILE
GV$BACKUP_DEVICE
GV$BACKUP_PIECE
GV$BACKUP_REDOLOG
GV$BACKUP_SET
GV$BACKUP_SPFILE
GV$BACKUP_SYNC_IO
GV$BH
GV$BGPROCESS
...
V$ACCESS
V$ACTIVE_SESSION_HISTORY
V$ARCHIVE
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVED_LOG
V$ASM_ALIAS
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISK_STAT
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
...
X$BH
X$KCBFWAIT
X$KCBSW
X$KCCCP
X$KCCDI
X$KCCFE
X$KCCFN
...

Understanding Fixed Tables/Views

Section titled “Understanding Fixed Tables/Views”

View Categories

Section titled “View Categories”
  • V$ Views: Formatted, user-friendly views of system data
  • GV$ Views: Global views showing data from all RAC instances
  • X$ Tables: Internal kernel structures (requires special privileges)

Common Prefixes

Section titled “Common Prefixes”
  • ASM_: Automatic Storage Management related
  • ARCHIVE_: Archive log and backup information
  • BACKUP_: RMAN backup and recovery
  • DATABASE_: Database-wide information
  • DATAGUARD_: Data Guard specific views
  • DIAG_: Diagnostic and trace information
  • FLASHBACK_: Flashback technology views
  • INSTANCE_: Instance-level information
  • LOCK_: Locking and concurrency
  • LOG_: Redo log information
  • MEMORY_: Memory management
  • PARAMETER_: Configuration parameters
  • PROCESS_: Process information
  • RECOVERY_: Recovery operations
  • SEGMENT_: Segment and extent information
  • SESSION_: Session-related data
  • SGA_: System Global Area information
  • SQL_: SQL statement information
  • STREAMS_: Oracle Streams replication
  • SYSTEM_: System-wide statistics
  • TEMP_: Temporary space information
  • TRANSACTION_: Transaction information

Common Use Cases

Section titled “Common Use Cases”

  1. System Monitoring Script Development

    • Find available monitoring views
    • Discover new Oracle features
    • Validate view availability across versions

  2. Documentation and Learning

    • Create comprehensive system documentation
    • Study Oracle internals
    • Build monitoring toolkits

  3. Troubleshooting

    • Find appropriate diagnostic views
    • Locate specific system information
    • Verify view accessibility

Practical Examples

Section titled “Practical Examples”
Section titled “Find all ASM-related views:”
SELECT name
FROM v$fixed_table
WHERE name LIKE '%ASM%'
ORDER BY name;
Section titled “Find all session-related views:”
SELECT name
FROM v$fixed_table
WHERE name LIKE '%SESSION%'
ORDER BY name;

Find all X$ tables:

Section titled “Find all X$ tables:”
SELECT name
FROM v$fixed_table
WHERE name LIKE 'X$%'
ORDER BY name;

Version Differences

Section titled “Version Differences”

Different Oracle versions have different fixed tables:

  • 11g: ~500 fixed objects
  • 12c: ~600+ fixed objects
  • 19c: ~700+ fixed objects
  • 21c+: ~800+ fixed objects

New features typically add new V$ views.

Best Practices

Section titled “Best Practices”

  1. Reference Usage

    • Bookmark this output for quick reference
    • Use to validate monitoring scripts
    • Check availability before using in scripts

  2. Documentation

    • Include in system documentation
    • Note version-specific views
    • Cross-reference with Oracle documentation

  3. Script Development

    • Verify view existence before querying
    • Handle version differences gracefully
    • Use appropriate error handling
Section titled “Related Scripts”