Fixed Tables/Views Finder (vfixfind.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.

What This Script Does

This script searches Oracle’s fixed tables and views (V$ and X$ views) by name pattern, helping you discover available system views for monitoring and diagnostics. Essential for finding the right Oracle internal views for specific monitoring needs.

The Script

rem  vfixfind.sql
rem
ttitle 'Fixed Tables/Views'
rem
col name format a30 heading 'NAME'
rem

select
  name
  , type
from
  v$fixed_table
where
  name like nvl(upper('%&name%'),'%')
  and type = 'VIEW'
order by name
;

Parameters

The script prompts for:

• name: Partial view name to search for (optional, defaults to all views)

Usage

-- Basic usage (will prompt for name pattern)
@vfixfind.sql

-- Example values when prompted:
-- name: session (will find all views containing "session")
-- name: lock (will find all views containing "lock")
-- name: (empty for all fixed views)

Sample Output

                               Fixed Tables/Views

NAME                           TYPE
------------------------------ -----
V$SESSION                      VIEW
V$SESSION_BLOCKERS             VIEW
V$SESSION_CONNECT_INFO         VIEW
V$SESSION_CURSOR_CACHE         VIEW
V$SESSION_EVENT                VIEW
V$SESSION_FIX_CONTROL          VIEW
V$SESSION_LONGOPS              VIEW
V$SESSION_OBJECT_CACHE         VIEW
V$SESSION_WAIT                 VIEW
V$SESSION_WAIT_CLASS           VIEW
V$SESSION_WAIT_HISTORY         VIEW