Fixed View Definitions (vfixview.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Fixed View Definitions

This script retrieves the SQL definitions of Oracle fixed views (V$ views), providing insight into how Oracle constructs these internal views and the underlying data structures they access.

Script: vfixview.sql

rem  vfixview.sql
rem
ttitle 'Fixed View Definitions'
rem
col view_name format a30 heading 'VIEW NAME'
col view_definition format a80 heading 'VIEW DEFINITION'
rem
select view_name, view_definition
  from v$fixed_view_definition
 where view_name like upper('&view')
 order by view_name
/

What This Script Does

This script provides access to Oracle’s fixed view definitions by:

• Interactive View Lookup: Prompts for a view name pattern using wildcards
• Definition Display: Shows the actual SQL definition used to create the view
• Pattern Matching: Uses LIKE operator to find views matching a pattern
• Alphabetical Listing: Orders results by view name for easy browsing
• Formatted Output: Clean, readable display of view names and definitions

Usage

-- Run the script and enter a view name pattern when prompted
@vfixview.sql
Enter value for view: SESSION

-- Use wildcards to find related views
@vfixview.sql
Enter value for view: %LOCK%

-- Get definition for a specific view
@vfixview.sql
Enter value for view: V$SESSION

-- Direct usage without prompting
SELECT view_name, view_definition
FROM v$fixed_view_definition
WHERE view_name = 'V$SESSION';

Required Privileges

• SELECT privilege on V$FIXED_VIEW_DEFINITION
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

                           Fixed View Definitions

VIEW NAME                      VIEW DEFINITION
------------------------------ -------------------------------------------------------------------------
V$SESSION                      select s.addr,s.ksuseser,s.ksusetim,u.user#,u.username,
                              s.ksuseusr,s.machine,s.terminal,s.program,s.type,
                              s.sql_address,s.sql_hash_value,s.sql_id,s.sql_child_number,
                              s.sql_exec_start,s.sql_exec_id,s.prev_sql_addr,
                              s.prev_hash_value,s.prev_sql_id,s.prev_child_number,
                              s.prev_exec_start,s.prev_exec_id,s.plsql_entry_object_id,
                              s.plsql_entry_subprogram_id,s.plsql_object_id,
                              s.plsql_subprogram_id,s.module,s.module_hash,s.action,
                              s.action_hash,s.client_info,s.fixed_table_sequence,
                              s.row_wait_obj#,s.row_wait_file#,s.row_wait_block#,
                              s.row_wait_row#,s.top_level_call#,s.logon_time,
                              s.last_call_et,s.pdml_enabled,s.failover_type,
                              s.failover_method,s.failed_over,s.resource_consumer_group,
                              s.pdml_status,s.pddl_status,s.pq_status,s.current_queue_duration,
                              s.client_identifier,s.blocking_session_status,
                              s.blocking_instance,s.blocking_session,s.final_blocking_session_status,
                              s.final_blocking_instance,s.final_blocking_session,s.seq#,s.event#,
                              s.event,s.p1text,s.p1,s.p1raw,s.p2text,s.p2,s.p2raw,s.p3text,s.p3,
                              s.p3raw,s.wait_class_id,s.wait_class#,s.wait_class,s.wait_time,
                              s.seconds_in_wait,s.state,s.service_name,s.sql_trace,
                              s.sql_trace_waits,s.sql_trace_binds,s.sql_trace_plan_stats,
                              s.session_edition_id,s.creator_addr,s.creator_serial#,
                              s.ecid,s.sql_translation_profile_id,s.pga_tunable_mem,
                              s.pga_used_mem,s.pga_alloc_mem,s.pga_freeable_mem,
                              s.pga_max_mem from v$session_internal s, user$ u
                              where s.user# = u.user#

V$SESSION_CONNECT_INFO         select s.sid,s.serial#,s.authentication_type,
                              s.osuser,s.network_service_banner from v$session s
                              where s.type != 'BACKGROUND'

Understanding the Output

View Name

• Shows the exact name of the fixed view
• Always prefixed with ‘V$’ for fixed views
• Corresponds to dictionary views with ‘V_$’ prefix

View Definition

• Complete SQL statement used to create the view
• Shows underlying tables and joins
• Reveals column mappings and transformations
• May reference other internal views or base tables

Common Use Cases

1. Understanding V$ Views Structure

-- Learn how V$SESSION is constructed
SELECT view_name, view_definition
FROM v$fixed_view_definition
WHERE view_name = 'V$SESSION';

-- Understand locking views
SELECT view_name, view_definition
FROM v$fixed_view_definition
WHERE view_name LIKE '%LOCK%'
ORDER BY view_name;

2. Finding Related Views

-- Find all memory-related views
SELECT view_name, SUBSTR(view_definition, 1, 100) || '...' as definition_preview
FROM v$fixed_view_definition
WHERE view_name LIKE '%MEMORY%'
   OR view_name LIKE '%SGA%'
   OR view_name LIKE '%PGA%'
ORDER BY view_name;

3. Exploring Performance Views

-- Find all wait event related views
SELECT view_name
FROM v$fixed_view_definition
WHERE UPPER(view_definition) LIKE '%WAIT%'
   OR view_name LIKE '%WAIT%'
   OR view_name LIKE '%EVENT%'
ORDER BY view_name;

4. Database Discovery

-- Find all available fixed views
SELECT COUNT(*) as total_fixed_views
FROM v$fixed_view_definition;

-- Get a sample of view names by category
SELECT
  CASE
    WHEN view_name LIKE '%SESSION%' THEN 'Session Views'
    WHEN view_name LIKE '%LOCK%' OR view_name LIKE '%LATCH%' THEN 'Locking Views'
    WHEN view_name LIKE '%SQL%' THEN 'SQL Views'
    WHEN view_name LIKE '%MEMORY%' OR view_name LIKE '%SGA%' OR view_name LIKE '%PGA%' THEN 'Memory Views'
    WHEN view_name LIKE '%FILE%' OR view_name LIKE '%IO%' THEN 'I/O Views'
    ELSE 'Other Views'
  END as category,
  COUNT(*) as view_count
FROM v$fixed_view_definition
GROUP BY
  CASE
    WHEN view_name LIKE '%SESSION%' THEN 'Session Views'
    WHEN view_name LIKE '%LOCK%' OR view_name LIKE '%LATCH%' THEN 'Locking Views'
    WHEN view_name LIKE '%SQL%' THEN 'SQL Views'
    WHEN view_name LIKE '%MEMORY%' OR view_name LIKE '%SGA%' OR view_name LIKE '%PGA%' THEN 'Memory Views'
    WHEN view_name LIKE '%FILE%' OR view_name LIKE '%IO%' THEN 'I/O Views'
    ELSE 'Other Views'
  END
ORDER BY view_count DESC;

Advanced Analysis

1. Understanding View Dependencies

-- Find views that reference other V$ views
SELECT view_name,
       CASE
         WHEN UPPER(view_definition) LIKE '%V$%' THEN 'References other V$ views'
         ELSE 'Base view'
       END as dependency_type
FROM v$fixed_view_definition
WHERE UPPER(view_definition) LIKE '%V$%'
ORDER BY view_name;

2. Analyze Complex Views

-- Find views with complex definitions (long SQL)
SELECT view_name,
       LENGTH(view_definition) as definition_length,
       CASE
         WHEN LENGTH(view_definition) > 5000 THEN 'Very Complex'
         WHEN LENGTH(view_definition) > 2000 THEN 'Complex'
         WHEN LENGTH(view_definition) > 500 THEN 'Moderate'
         ELSE 'Simple'
       END as complexity
FROM v$fixed_view_definition
ORDER BY LENGTH(view_definition) DESC;

3. Find Views Using Specific Tables

-- Find views that use X$ tables
SELECT view_name,
       SUBSTR(view_definition, 1, 200) as definition_start
FROM v$fixed_view_definition
WHERE UPPER(view_definition) LIKE '%X$%'
ORDER BY view_name;

Educational Value

Understanding Oracle Internals

• Architecture Insight: Learn how Oracle exposes internal data
• Join Patterns: See common join patterns used in system views
• Column Mapping: Understand how internal columns map to user-visible columns
• Performance Implications: See which views involve complex operations

Troubleshooting Benefits

• View Behavior: Understand why certain views perform differently
• Data Sources: Identify the source tables for specific information
• Filtering Logic: See built-in filtering in view definitions
• Relationship Understanding: Learn how different views relate to each other

Oracle Version Considerations

View Evolution

Different Oracle versions may have:

• New Views: Additional V$ views for new features
• Modified Definitions: Changes to existing view definitions
• Deprecated Views: Some views may be removed or deprecated
• Enhanced Columns: Additional columns in existing views

Version Comparison

-- Document current version's views
SPOOL fixed_views_current_version.txt
SELECT view_name FROM v$fixed_view_definition ORDER BY view_name;
SPOOL OFF

-- Compare with documentation or other versions

Integration with Other Scripts

This script works well with:

• Discover Undocumented V$ Views (vnew.sql) - Find undocumented views
• Database Information (db.sql) - Overall database analysis
• System Parameter Analysis (xparmall.run) - For parameter definitions
• Dictionary Analysis (vdict.sql) - For data dictionary exploration

Best Practices

1. Educational Use: Primarily for learning Oracle internals
2. Documentation: Use to document custom monitoring scripts
3. Troubleshooting: Understand data sources for performance analysis
4. Version Tracking: Document view changes across Oracle versions
5. Custom Views: Use patterns for creating custom monitoring views

Security and Support Considerations

Important Notes

• Educational Purpose: These definitions are for learning and understanding
• Unsupported Features: Oracle doesn’t support direct access to underlying tables
• Version Dependency: Definitions may change between Oracle versions
• Internal Changes: Oracle may modify internal structures without notice

This script provides valuable insight into Oracle’s internal architecture and is an excellent learning tool for understanding how Oracle exposes system information through V$ views.