Discover Undocumented V$ Views (vnew.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.

Discover Undocumented V$ Views

This script discovers Oracle V$ views that exist in the database but aren’t officially documented in the data dictionary. It’s an excellent tool for finding new Oracle features and undocumented functionality.

Script: vnew.sql

rem  vnew.sql
rem
select substr( name, 3 )
  from v$fixed_table
 where type = 'VIEW'
minus
select substr( view_name, 4 )
  from sys.dba_views
 where owner = 'SYS'
   and view_name like 'V_$%'
/

What This Script Does

This script uses a MINUS operation to find V$ views that exist in Oracle’s internal fixed table registry (v$fixed_table) but aren’t documented in the official data dictionary view (dba_views). These are typically:

• New features in recent Oracle versions not yet documented
• Undocumented views for internal Oracle operations
• Diagnostic views available but not publicly documented
• Beta features included in the database

Usage

-- Run the script to find undocumented views
@vnew.sql

-- To get more details about discovered views, try:
-- (Replace VIEW_NAME with an actual view found)
DESC V$VIEW_NAME;
SELECT * FROM V$VIEW_NAME WHERE ROWNUM <= 5;

Required Privileges

• SELECT privilege on V$FIXED_TABLE
• SELECT privilege on DBA_VIEWS
• Typically requires DBA role or equivalent

Sample Output

SUBSTR(NAME,3)
--------------
ASM_DISK_IOSTAT
ASM_DISK_STAT_SUMMARY
BACKUP_ASYNC_IO
CLUSTER_INTERCONNECTS
CON_SYSMETRIC
DATABASE_BLOCK_CORRUPTION
DIAG_ALERT_EXT
FLUSH_HISTOGRAM
HM_CHECK_PARAM
MEMORY_CURRENT_RESIZE_OPS
PERSISTENT_QMN_CACHE
QUARANTINE
RESTORE_POINT_LOG
SQL_BIND_DATA
STREAMS_POOL_ADVICE
TEMPSEG_USAGE
TRANSACTION_ENQUEUE
XS_SESSION_NS_ATTRIBUTES

Understanding the Results

Types of Undocumented Views

Performance and Diagnostics:

• Views ending in _STAT, _IOSTAT, _ADVICE
• Memory and resource monitoring views
• Performance tuning and analysis views

New Features:

• ASM (Automatic Storage Management) related views
• Streams and replication views
• Pluggable Database (PDB) related views

Internal Operations:

• Backup and recovery operation views
• Database maintenance views
• Lock and transaction management views

Common Use Cases

1. Discover New Oracle Version Features

-- Find views that might be new in your Oracle version
SELECT substr(name, 3) AS undocumented_view
FROM v$fixed_table
WHERE type = 'VIEW'
MINUS
SELECT substr(view_name, 4)
FROM sys.dba_views
WHERE owner = 'SYS'
  AND view_name LIKE 'V_$%'
ORDER BY 1;

2. Explore ASM-Related Views

-- Find undocumented ASM views
SELECT substr(name, 3) AS asm_view
FROM v$fixed_table
WHERE type = 'VIEW'
  AND UPPER(name) LIKE '%ASM%'
MINUS
SELECT substr(view_name, 4)
FROM sys.dba_views
WHERE owner = 'SYS'
  AND view_name LIKE 'V_$%'
  AND UPPER(view_name) LIKE '%ASM%';

3. Find Memory-Related Diagnostic Views

-- Find undocumented memory views
SELECT substr(name, 3) AS memory_view
FROM v$fixed_table
WHERE type = 'VIEW'
  AND (UPPER(name) LIKE '%MEMORY%' OR
       UPPER(name) LIKE '%SGA%' OR
       UPPER(name) LIKE '%PGA%')
MINUS
SELECT substr(view_name, 4)
FROM sys.dba_views
WHERE owner = 'SYS'
  AND view_name LIKE 'V_$%'
  AND (UPPER(view_name) LIKE '%MEMORY%' OR
       UPPER(view_name) LIKE '%SGA%' OR
       UPPER(view_name) LIKE '%PGA%');

4. Investigate Specific Undocumented Views

-- After finding an interesting view, explore its structure
-- Replace 'UNDOCUMENTED_VIEW' with actual view name
DESC V$UNDOCUMENTED_VIEW;

-- Sample a few rows to understand the data
SELECT * FROM V$UNDOCUMENTED_VIEW WHERE ROWNUM <= 10;

-- Count total rows
SELECT COUNT(*) FROM V$UNDOCUMENTED_VIEW;

Practical Examples

Example 1: Exploring V$ASM_DISK_IOSTAT

-- This view provides detailed ASM disk I/O statistics
SELECT disk_group_name, disk_name,
       reads, writes, read_bytes, write_bytes,
       read_time, write_time
FROM v$asm_disk_iostat
WHERE reads > 0 OR writes > 0;

Example 2: Using V$MEMORY_CURRENT_RESIZE_OPS

-- Shows current memory resize operations
SELECT component, oper_type, oper_mode,
       initial_size, target_size, final_size,
       status, start_time
FROM v$memory_current_resize_ops;

Example 3: Checking V$SQL_BIND_DATA

-- Provides bind variable data for SQL statements
SELECT sql_id, position, datatype_string,
       value_string, value_anydata
FROM v$sql_bind_data
WHERE sql_id = '&sql_id';

Oracle Version Considerations

Oracle 12c and Later

• Many PDB (Pluggable Database) related views
• Enhanced ASM monitoring views
• Improved memory management views

Oracle 19c and Later

• Machine learning related views
• Autonomous database features
• Enhanced security and audit views

Oracle 21c and Later

• Blockchain and immutable table views
• Enhanced JSON functionality views
• Graph database related views

Caution and Best Practices

Important Warnings

1. Undocumented Features: These views are not officially supported
2. Version Dependencies: Views may disappear in future Oracle versions
3. No Documentation: Oracle doesn’t provide official documentation
4. Stability: Structure and content may change without notice
5. Support: Oracle Support may not help with undocumented views

Best Practices

1. Development Only: Use primarily in development/test environments
2. Document Findings: Keep notes on useful undocumented views
3. Version Testing: Test across Oracle versions before relying on them
4. Alternative Solutions: Always have documented alternatives
5. Regular Checks: Periodically verify views still exist

Integration with Other Discovery Scripts

This script works well with:

• Advanced Parameters View (xparmall.run) - For undocumented parameters
• Fixed View Definitions (vfixview.sql) - For understanding internal views
• Database Health Check (db.sql) - For comprehensive analysis

Advanced Usage

Create a Monitoring Script

-- Save this as monitor_new_views.sql
SET PAGESIZE 1000
SET LINESIZE 120
COLUMN view_name FORMAT A40
COLUMN discovered_date FORMAT A12

SELECT substr(name, 3) AS view_name,
       SYSDATE AS discovered_date
FROM v$fixed_table
WHERE type = 'VIEW'
MINUS
SELECT substr(view_name, 4),
       SYSDATE
FROM sys.dba_views
WHERE owner = 'SYS'
  AND view_name LIKE 'V_$%'
ORDER BY 1;

Create Documentation Template

-- For each discovered view, run this template
PROMPT ========================================
PROMPT Analyzing undocumented view: V$&view_name
PROMPT ========================================

DESC V$&view_name;

PROMPT Sample data:
SELECT * FROM V$&view_name WHERE ROWNUM <= 5;

PROMPT Row count:
SELECT COUNT(*) AS total_rows FROM V$&view_name;

This script is invaluable for Oracle professionals who want to stay on the cutting edge of Oracle technology and discover hidden gems within the database.