ORACLE Data Dictionary Views
Redo Log Data Dictionary Views
| View | Description |
|---|---|
V$LOG |
Displays the redo log file information from the control file |
V$LOGFILE |
Identifies redo log groups and members and member status |
V$LOG_HISTORY |
Contains log history information |
Undo Space Data Dictionary Views
| View | Description |
|---|---|
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. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode. |
V$ROLLSTAT |
For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace |
V$TRANSACTION |
Contains undo segment information |
DBA_UNDO_EXTENTS |
Shows the status and size of each extent in the undo tablespace. |
DBA_HIST_UNDOSTAT |
Contains statistical snapshots of V$UNDOSTAT information. See Oracle Database 2 Day DBA for more information. |
Control Files Data Dictionary Views
| View | Description |
|---|---|
V$DATABASE |
Displays database information from the control file |
V$CONTROLFILE |
Lists the names of control files |
V$CONTROLFILE_RECORD_SECTION |
Displays information about control file record sections |
V$PARAMETER |
Displays the names of control files as specified in the CONTROL_FILES initialization parameter |
Tablespace Data Dictionary Views
| View | Description |
|---|---|
V$TABLESPACE |
Name and number of all tablespaces from the control file. |
V$ENCRYPTED_TABLESPACES |
Name and encryption algorithm of all encrypted tablespaces. |
DBA_TABLESPACES, USER_TABLESPACES |
Descriptions of all (or user accessible) tablespaces. |
DBA_TABLESPACE_GROUPS |
Displays the tablespace groups and the tablespaces that belong to them. |
DBA_SEGMENTS, USER_SEGMENTS |
Information about segments within all (or user accessible) tablespaces. |
DBA_EXTENTS, USER_EXTENTS |
Information about data extents within all (or user accessible) tablespaces. |
DBA_FREE_SPACE, USER_FREE_SPACE |
Information about free extents within all (or user accessible) tablespaces. |
DBA_TEMP_FREE_SPACE |
Displays the total allocated and free space in each temporary tablespace. |
V$DATAFILE |
Information about all datafiles, including tablespace number of owning tablespace. |
V$TEMPFILE |
Information about all tempfiles, including tablespace number of owning tablespace. |
DBA_DATA_FILES |
Shows files (datafiles) belonging to tablespaces. |
DBA_TEMP_FILES |
Shows files (tempfiles) belonging to temporary tablespaces. |
V$TEMP_EXTENT_MAP |
Information for all extents in all locally managed temporary tablespaces. |
V$TEMP_EXTENT_POOL |
For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance. |
V$TEMP_SPACE_HEADER |
Shows space used/free for each tempfile. |
DBA_USERS |
Default and temporary tablespaces for all users. |
DBA_TS_QUOTAS |
Lists tablespace quotas for all users. |
V$SORT_SEGMENT |
Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type. |
V$TEMPSEG_USAGE |
Describes temporary (sort) segment usage by user for temporary or permanent tablespaces. |
Datafiles Data Dictionary Views
The following data dictionary views provide useful information about the datafiles of a database:
| View | Description |
|---|---|
DBA_DATA_FILES |
Provides descriptive information about each datafile, including the tablespace to which it belongs and the file ID. The file ID can be used to join with other views for detail information. |
DBA_EXTENTS
|
DBA view describes the extents comprising all segments in the database. Contains the file ID of the datafile containing the extent. USER view describes extents of the segments belonging to objects owned by the current user. |
DBA_FREE_SPACE
|
DBA view lists the free extents in all tablespaces. Includes the file ID of the datafile containing the extent.USER view lists the free extents in the tablespaces accessible to the current user. |
V$DATAFILE |
Contains datafile information from the control file |
V$DATAFILE_HEADER |
Contains information from datafile headers |
Process and Session Data Dictionary Views
| View | Description |
|---|---|
V$PROCESS |
Contains information about the currently active processes |
V$SESSION |
Lists session information for each current session |
V$SESS_IO |
Contains I/O statistics for each user session |
V$SESSION_LONGOPS |
Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release. |
V$SESSION_WAIT |
Displays the current or last wait for each session |
V$SESSION_WAIT_HISTORY |
Lists the last ten wait events for each active session |
V$WAIT_CHAINS |
Displays information about blocked sessions |
V$SYSSTAT |
Contains session statistics |
V$RESOURCE_LIMIT |
Provides information about current and maximum global resource utilization for some system resources |
V$SQLAREA |
Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution |
Memory Management Data Dictionary Views
| View | Description |
|---|---|
V$SGA |
Displays summary information about the system global area (SGA). |
V$SGAINFO |
Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory. |
V$SGASTAT |
Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool. |
V$PGASTAT |
Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup. |
V$MEMORY_DYNAMIC_COMPONENTS |
Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each. |
V$SGA_DYNAMIC_COMPONENTS |
Displays the current sizes of all SGA components, and the last operation for each component. |
V$SGA_DYNAMIC_FREE_MEMORY |
Displays information about the amount of SGA memory available for future dynamic SGA resize operations. |
V$MEMORY_CURRENT_RESIZE_OPS |
Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component. |
V$SGA_CURRENT_RESIZE_OPS |
Displays information about dynamic SGA component resize operations that are currently in progress. |
V$MEMORY_RESIZE_OPS |
Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET. |
V$SGA_RESIZE_OPS |
Displays information about the last 800 completed SGA component resize operations. |
V$MEMORY_TARGET_ADVICE |
Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management. |
V$SGA_TARGET_ADVICE |
Displays information that helps you tune SGA_TARGET. |
V$PGA_TARGET_ADVICE |
Displays information that helps you tune PGA_AGGREGATE_TARGET. |
Performance Monitoring Data Dictionary Views
| View | Description |
|---|---|
V$LOCK |
Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch |
DBA_BLOCKERS |
Displays a session if it is holding a lock on an object for which another session is waiting |
DBA_WAITERS |
Displays a session if it is waiting for a locked object |
DBA_DDL_LOCKS |
Lists all DDL locks held in the database and all outstanding requests for a DDL lock |
DBA_DML_LOCKS |
Lists all DML locks held in the database and all outstanding requests for a DML lock |
DBA_LOCK |
Lists all locks or latches held in the database and all outstanding requests for a lock or latch |
DBA_LOCK_INTERNAL |
Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch |
V$LOCKED_OBJECT |
Lists all locks acquired by every transaction on the system |
V$SESSION_WAIT |
Lists the resources or events for which active sessions are waiting |
V$SYSSTAT |
Contains session statistics |
V$RESOURCE_LIMIT |
Provides information about current and maximum global resource utilization for some system resources |
V$SQLAREA |
Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution |
V$LATCH |
Contains statistics for nonparent latches and summary statistics for parent latches |
Tables Data Dictionary Views
Indexes Data Dictionary Views
| View | Description |
|---|---|
DBA_INDEXES
|
DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. |
DBA_IND_COLUMNS
|
These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. |
DBA_IND_EXPRESSIONS
|
These views describe the expressions of function-based indexes on tables. |
DBA_IND_STATISTICS
|
These views contain optimizer statistics for indexes. |
INDEX_STATS |
Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement. |
INDEX_HISTOGRAM |
Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement. |
V$OBJECT_USAGE |
Contains index usage information produced by the ALTER INDEX...MONITORING USAGE functionality. |
Clusters Data Dictionary Views
| View | Description |
|---|---|
DBA_CLUSTERS
|
DBA view describes all clusters in the database. ALL view describes all clusters accessible to the user. USER view is restricted to clusters owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. |
DBA_CLU_COLUMNS
|
These views map table columns to cluster columns |
Hash Clusters Data Dictionary Views
| View | Description |
|---|---|
DBA_CLUSTERS
|
DBA view describes all clusters (including hash clusters) in the database. ALL view describes all clusters accessible to the user. USER view is restricted to clusters owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package orANALYZE statement. |
DBA_CLU_COLUMNS
|
These views map table columns to cluster columns. |
DBA_CLUSTER_HASH_EXPRESSIONS
|
These views list hash functions for hash clusters. |
Views, Synonyms, and Sequences Data Dictionary Views
| View | Description |
|---|---|
DBA_VIEWS
|
DBA view describes all views in the database. ALL view is restricted to views accessible to the current user.USER view is restricted to views owned by the current user. |
DBA_SYNONYMS
|
These views describe synonyms. |
DBA_SEQUENCES
|
These views describe sequences. |
DBA_UPDATABLE_COLUMNS
|
These views describe all columns in join views that are updatable. |
