Data Dictionary Search (vdict.sql)

What This Script Does

This script searches the data dictionary for views and tables matching a specified pattern, focusing on V$ views by default. Essential for discovering available data dictionary views and understanding their purpose.

The Script

rem  vdict.sql
rem
ttitle 'DBA Tables'
rem
set linesize 200
col TABLE_NAME format a30 heading 'NAME'
col COMMENTS format a150
rem

select
  TABLE_NAME
  , COMMENTS
from
  DICT
where
  TABLE_NAME like nvl(upper('V_%&table_name%'),'%')
order by
  TABLE_NAME
;

Parameters

The script prompts for:

table_name: Partial table name to search for (optional, defaults to all V$ views)

Usage

-- Basic usage (will prompt for table name)
@vdict.sql

-- Example values when prompted:
-- table_name: session (will find V$SESSION related views)
-- table_name: (empty for all V$ views)

Sample Output

                               DBA Tables

NAME                           COMMENTS
------------------------------ ------------------------------------------------------------------------------
V$SESSION                      Session information including user, SQL, and wait statistics
V$SESSION_BLOCKERS             Information about sessions that are blocking other sessions
V$SESSION_CONNECT_INFO         Connection information for each session
V$SESSION_CURSOR_CACHE         Cursor cache statistics for each session
V$SESSION_EVENT                Wait events for each session
V$SESSION_FIX_CONTROL          Session-level fix control settings
V$SESSION_LONGOPS              Long running operations for each session
V$SESSION_OBJECT_CACHE         Object cache statistics for each session
V$SESSION_WAIT                 Current wait information for each session
V$SESSION_WAIT_CLASS           Wait class statistics for each session
V$SESSION_WAIT_HISTORY         Wait event history for each session