Data Dictionary

 

V$ views

Configuration Constraints Indexes Jobs Locks Memory & SGA Multi Threaded Server Procedures & Packages Redo Log files Rollback Segments & Undo Resource Management Security Storage Snapshots

Advanced Queuing

DBA_QUEUE_SCHEDULES
Shows when particular queued messages are to be delivered.

DBA_QUEUE_TABLES
List the tables used to hold the queues defined as part of the Advanced Queung facility.

DBA_QUEUES
Lists the queues defined as part of the Advanced Queuing facility.

Auditing

DBA_AUDIT_TRAIL

DBA_AUDIT_SESSION

DBA_AUDIT_OBJECT

DBA_AUDIT_STATEMENT

Configuration

V$COMPATIBILITY
Lists features in use by current instance that would preclude reverting to a previous release of the Oracle software. Since these are instance based some of these may disappear if the database is shut down normally.

V$COMPATSEG
Lists permanent features in the database that would preclude reverting to a previous release of Oracle

V$EVENT_NAME
Contains information on all possible wait events.

V$LICENSE
Contains a single row specifying the maximum numbers of concurrent and named users allowed, as well as the highwater marks.

V$MLS_PARAMETERS
Lists Trusted Oracle Parameters.

V$NLS_PARAMETERS
Contains the current values for each National Language Support parameters.

V$NLS_VALID_VALUES
Lists the valid values that each NLS parameter can take.

V$OPTION
Lists the Oracle Options installed

V$PARAMETER
Lists all INIT>ORA parameters, their values, if they are set to default and if they are modifiable through ALTER SYSTEM or ALTER SESSION.

V$STATNAME
Lists the name for each statistic stored in V$SYSSTAT and V$SESSSTAT.

V$SYSTEM_PARAMETER
Same as V$PARAMETER.

V$VERSION
Lists the current version numbers of the library components of the Oracle kernal.

Constraints

DBA_CONS_COLUMNS
Shows which columns are affected by each constraint.

DBA_CONSTRAINTS
Lists all constraints

DBA_DEPENDENCIES
Lists dependencies between database objects.

Indexes

DBA_INDEXES

DBA_IND_COLUMNS

DBA_PART_INDEXES

DBA_IND_PARTITIONS

INDEX_HISTOGRAM
Info about the distribution of index keys within the table (populated one index at a time by ANALYZE INDEX.. VALIDATE STRUCTURE).

INDEX_STATS
Info about the structure of an index (populated one index at a time by ANALYZE INDEX.. VALIDATE STRUCTURE).

Jobs

DBA_JOBS
All defined jobs

DBA_JOBS_RUNNING
All currently running jobs

Large Objects (LOBs)

DBA_DIRECTORIES
Lists all defined external directories (where BFILEs are formed).

DBA_LOBS
Lists all large objects defined.

Locks

DBA_BLOCKERS
Lists all sessions holding locks for whose release others are waiting.

DBA_LOCKS
All locks held or requested.

DBA_DDL_LOCKS
All DDL Locks.

DBA_DML_LOCKS
All DML Locks.

DBA_WAITERS
Lists all sessions that are waiting on a lock held by another session. DBMS_LOCK_ALLOCATED
Shows which locks the current user has allocated.

Memory

These views provide information about the System Global Area:
V$CURRENT_BUCKET
Lists the number of increased buffer misses if the value of DB_BLOCK_BUFFERS were to be reduced. See INIT.ORA paramater DB_BLOCK_EXTEND.


V$RECENT_BUCKET
Lists the number of increades buffer hits if the value of DB_BLOCK_BUFFERS were to be increased. See the INIT.ORA parameter DB_BLOCK_LRU_EXTENDED_STATISTICS.


V$SGA
Contains information about the size, in bytes, of each of the various SGA components.


v$SGASTAT
Provides more detailed information about SGA utilization than V$SGA. Shows the breakdown of the SHARED_POOL and LARGE_POOL areas.


V$SHARED_POOL_RESERVED
Contains statistics about the SHARED_POOL area of the SGA. Some of the columns are meaningful only if the INIT.ORA parameter SHARED_POOL_RESERVED_SIZE has been set.

Multi Threaded Server

V$CIRCUIT
Contains information about the virtual circuits used to connect users to the instance.

V$DISPATCHER
Provides information about the various configured dispatcher processes.

V$DISPATCHER_RATE
Provides statistics about the dispatcher process throughput.

V$MTS
Information about the overall activity of MTS.

V$QUEUE
Statistics about the MTS message queue.

V$REQDIST
12 bucket histogram of the distribution of request service times.

V$SHARED_SERVER
Shows the status of each of the shared servers.

Procedures / Packages

DBA_ERRORS
Stored object compilation errors.

DBA_OBJECTS
All objects and their status. object_type in ('PACKAGE', PACKAGE BODY')

DBA_OBJECT_SIZE
Shows the size of the compiled code.

DBA_SOURCE
Source SQL.

DBA_LIBRARIES

ALL_ARGUMENTS

PUBLIC_DEPENDENCIES

Redo Logs

v$log - Redo log file information from the control file.

v$logmnr_contents - Log history information.

v$logmnr_logs - Log history information, oldest and most recent times any updates to the log, scn and database information.

v$logmnr_dictionary

v$lomnr_parameters

v$log_history

Rollback Segments

DBA_ROLLBACK_SEGMENTS
In addition DBA_SEGMENTS shows information about all segments including rollbacks.

DBA_UNDO_EXTENTS
Shows the commit time for each extent in the undo tablespace.

V$ROLLSTAT
To look for contention look at the "waits" column.

V$WAITSTAT
To look for contention look at the "buffer busy waits".

V$UNDOSTAT
Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. Oracle also uses this information to help tune undo usage in the system. This view is available in both the automatic undo management and the manual undo management modes.

V$ROLLSTAT
For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace

V$TRANSACTION
Contains undo segment information

Resource Management

DBA_RSRC_CONSUMER_GROUP_PRIVS
lists all resource consumer groups and the users and roles to which they have been granted. The equivalent user view lists all consumer groups the user has been granted to.

DBA_RSRC_CONSUMER_GROUPS
Lists all resource consumer groups that exist in the database.

DBA_RSRC_MANAGER_SYSTEM_PRIVS
lists all users and roles that have been granted Database Resource Manager system privileges.

DBA_RSRC_PLAN_DIRECTIVES
Lists all resource plan directives that exist in the database.

DBA_RSRC_PLANS
List all resource plans that exist in the database.

DBA_USERS
Contains information about all users of the database. Specifically, for the Database Resource Manager, it contains the initial resource consumer group for the user.

V$ACTIVE_SESS_POOL_MTH
Displays all available active session pool resource allocation methods.

V$PARALLEL_DEGREE_LIMIT_MTH
Displays all available parallel degree limit resource allocation methods.

V$QUEUEING
Displays all available queuing resource allocation methods.

V$RSRC_CONSUMER_GROUP
Displays information about active resource consumer groups. This view can be used for tuning.

V$RSRC_CONSUMER_GROUP_CPU_MTH
Displays all available CPU resource allocation methods for resource consumer groups.

V$RSRC_PLAN
Displays the names of all currently active resource plans.

V$RSRC_PLAN_CPU_MTH
Displays all available CPU resource allocation methods for resource plans.

V$SESSION
Lists session information for each current session. Specifically, lists the name of each current session's resource consumer group.

Security

Listing Grants

DBA_ROLES
Names of roles


DBA_ROLE_PRIVS

DBA_SYS_PRIVS

DBA_TAB_PRIVS

DBA_COL_PRIVS

ROLE_ROLE_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

V$ENABLEDPRIVS
Lists all system privileges that are enabled for the current session (both explicitly and implicitly granted.

V$PWFILE_USERS
Lists all users who have been identified in the password file as having SYSDBA or SYSOPER privileges.

Sequences

DBA_SEQUENCES

Storage

DBA_DATA_FILES
Lists all data files in use by the database.

DBA_EXTENTS
Lists every allocated extent for every segment.

DBA_FREE_SPACE

Lists every free extent. With DBA_EXTENTS should
account for all storage in DBA_DATA_FILES.
DBA_FREE_SPACE_COALESCED
Lists every extent that is the start of a block of free extents

DBA_ROLLBACK_SEGS
Lists all rollback segments

DBA_SEGMENTS

DBA_TABLESPACES

DBA_TS_QUOTAS
Shows the granted quota and used storage in tablespaces by user.

Snapshots

DBA_RCHILD

DBA_REFRESH

DBA_REFRESH_CHILDREN

DBA_REGISTERED_SNAPSHOTS

DBA_RGROUP

DBA_SNAPSHOT_LOGS

DBA_SNAPSHOT_REFRESH_TIMES

DBA_SNAPSHOTS

V$ Views

V$LOCK
Who has what locked. Row level locks are not stored here, they are kept within the data.

V$MYSTAT
Statistics for your session only. To allow a user access to this you must grant select on v_$statname and v_$mystat.

V$OPEN_CURSOR
This contains a list of every open cursor. They will often remain cached event after they have been closed.

V$SESSION
A row for every session open.

V$SESSION_WAIT
This is used to show all sessions currently waiting for something. Good place to start for databases that are hung or running slowly.

V$SESSTAT
Similar to MYSTAT but for all sessions.

V$SESS_IO

V$SQL, V$SQLAREA
All SQL that is parsed and stored in the shared pool. SQLAREA is an aggregate view, querying it can be quite expensive.

V$STATNAME
Shows the mapping from the statistic number to the statistic name, it is useful when joined with V$MYSTAT and V$SESSTAT to turn the statistic number into a readable name.

V$SYSSTAT
Where SESSTAT keeps statistics per session V$SYSSTAT keeps them for an instance. This is the data used by statspack.

V$SYSTEM_EVENT
This is to events what V$SYSSTAT is to statistics. It contains instance level wait event information.