Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Data Dictionary Views

</p> <p>Data Dictionary Views</p> <p>



Also see How to debug PL/SQL code


Parameter Settings
Online Redo Log Information
archived redo logs
Job Queue Information
Tablespace Information
Datafile Information
Rollback Segment Information
Information about Tables
Index Information
Partitioned Tables and Indexes
Views, Synonyms, and Sequences
Constraint Information
Database Users and Profiles
Privilege and Role Information


Viewing Parameter Settings

You have several options for viewing parameter settings.

Method

Description

SHOW PARAMETERS

This command is used in sql*plus

CREATE PFILE

This SQL statement creates a text initialization parameter file from the binary server parameter file.

V$PARAMETER

This view displays the currently in effect parameter values.

V$PARAMETER2

This view displays the currently in effect parameter values. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.

V$SPPARAMETER

This view displays the current contents of the server parameter file. The view returns NULL values if a server parameter file is not being used by the instance.

Viewing Online Redo Log Information

Use the following views to display online redo log information. See Example

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

Viewing information about archived redo logs

There are several dynamic performance views that contain useful information about archived redo logs. See Example

Dynamic Performance View

Description

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all online redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.

The ARCHIVE LOG LIST Command

The SQL*Plus command ARCHIVE LOG LIST can be used to show archiving information for the connected instance. For example:

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

  • The database is currently operating in ARCHIVELOG mode.
  • Automatic archiving is enabled.
  • The archived redo log’s destination is D:\ORANT\oradata\IDDB2\archive.
  • The oldest filled online redo log group has a sequence number of 11160.
  • The next filled online redo log group to archive has a sequence number of 11163.
  • The current online redo log file has a sequence number of 11163.


    Viewing Job Queue Information

    You can view information about jobs in the job queue using the data dictionary views listed below: See Example

    View

    Description

    DBA_JOBS

    ALL_JOBS

    USER_JOBS

    DBA view describes all the jobs in the database. ALL view describes all jobs that are accessible to the current user. USER view describes all jobs owned by the current user.

    DBA_JOBS_RUNNING

    Lists all jobs in the database that are currently running. This view can be joined with V$LOCK to identify jobs that have locks.

    Viewing Tablespace Information

    The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database. See Example

    View

    Description

    V$TABLESPACE

    Name and number of all tablespaces from the control file.

    DBA_TABLESPACES, USER_TABLESPACES

    Descriptions of all (or user accessible) tablespaces.

    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.

    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$SORT_USER

    Temporary sort space usage by user and temporary/permanent tablespace.


    Viewing Datafile Information

    The following data dictionary views provide useful information about the datafiles of a database: See Example

    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

    USER_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

    USER_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


    Viewing Rollback Segment Information

    The following views are useful for displaying information about rollback segments: See Example

    View

    Description

    DBA_ROLLBACK_SEGS

    Describes the rollback segments, including names and tablespaces

    DBA_SEGMENTS

    Identifies a segment as a rollback segment and contains additional segment information

    V$ROLLNAME

    Lists the names of all online rollback segments

    V$ROLLSTAT

    Contains rollback segment statistics

    V$TRANSACTION

    Contains undo segment information

    In addition, the following data dictionary views contain information about the segments of a database, including rollback segments:

    • USER_SEGMENTS
    • DBA_SEGMENTS


    Viewing Information About Tables

    The following views allow you to access information about tables.

    View

    Description

    DBA_TABLES

    ALL_TABLES

    USER_TABLES

    DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.

    DBA_TAB_COLUMNS

    ALL_TAB_COLUMNS

    USER_TAB_COLUMNS

    These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.

    DBA_ALL_TABLES

    ALL_ALL_TABLES

    USER_ALL_TABLES

    These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.

    DBA_TAB_COMMENTS

    ALL_TAB_COMMENTS

    USER_TAB_COMMENTS

    These views display comments for tables and views. Comments are entered using the COMMENT statement.

    DBA_COL_COMMENTS

    ALL_COL_COMMENTS

    USER_COL_COMMENTS

    These views display comments for table and view columns. Comments are entered using the COMMENT statement.

    DBA_EXTERNAL_TABLES

    ALL_EXTERNAL_TABLES

    USER_EXTERNAL_TABLES

    These views list the specific attributes of external tables in the database.

    DBA_EXTERNAL_LOCATIONS

    ALL_EXTERNAL_LOCATIONS

    USER_EXTERNAL_LOCATIONS

    These views list the data sources for external tables.

    DBA_TAB_HISTOGRAMS

    ALL_TAB_HISTOGRAMS

    USER_TAB_HISTOGRAMS

    These views describe histograms on tables and views.

    DBA_TAB_COL_STATISTICS

    ALL_TAB_COL_STATISTICS

    USER_TAB_COL_STATISTICS

    These views provide column statistics and histogram information extracted from the related TAB_COLUMNS views.

    DBA_TAB_MODIFICATIONS

    ALL_TAB_MODIFICATIONS

    USER_TAB_MODIFICATIONS

    These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours).

    DBA_UNUSED_COL_TABS

    ALL_UNUSED_COL_TABS

    USER_UNUSED_COL_TABS

    These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSED statement.

    DBA_PARTIAL_DROP_TABS

    ALL_PARTIAL_DROP_TABS

    USER_PARTIAL_DROP_TABS

    These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system crash.


    Viewing Index Information

    The following views display information about indexes:

    View

    Description

    DBA_INDEXES

    ALL_INDEXES

    USER_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

    ALL_IND_COLUMNS

    USER_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

    ALL_IND_EXPRESSIONS

    USER_IND_EXPRESSIONS

    These views describe the expressions of function-based indexes on tables.

    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.


    Viewing Information About Partitioned Tables and Indexes

    The following views display information specific to partitioned tables and indexes:

    View

    Description

    DBA_PART_TABLES

    ALL_PART_TABLES

    USER_PART_TABLES

    DBA view displays partitioning information for all partitioned tables in the database. ALL view displays partitioning information for all partitioned tables accessible to the user. USER view is restricted to partitioning information for partitioned tables owned by the user.

    DBA_TAB_PARTITIONS

    ALL_TAB_PARTITIONS

    USER_TAB_PARTITIONS

    Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.

    DBA_TAB_SUBPARTITIONS

    ALL_TAB_SUBPARTITIONS

    USER_TAB_SUBPARTITIONS

    Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the DBMS_STATS package or the ANALYZE statement.

    DBA_PART_KEY_COLUMNS

    ALL_PART_KEY_COLUMNS

    USER_PART_KEY_COLUMNS

    Display the partitioning key columns for partitioned tables.

    DBA_SUBPART_KEY_COLUMNS

    ALL_SUBPART_KEY_COLUMNS

    USER_SUBPART_KEY_COLUMNS

    Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables).

    DBA_PART_COL_STATISTICS

    ALL_PART_COL_STATISTICS

    USER_PART_COL_STATISTICS

    Display column statistics and histogram information for the partitions of tables.

    DBA_SUBPART_COL_STATISTICS

    ALL_SUBPART_COL_STATISTICS

    USER_SUBPART_COL_STATISTICS

    Display column statistics and histogram information for subpartitions of tables.

    DBA_PART_HISTOGRAMS

    ALL_PART_HISTOGRAMS

    USER_PART_HISTOGRAMS

    Display the histogram data (end-points for each histogram) for histograms on table partitions.

    DBA_SUBPART_HISTOGRAMS

    ALL_SUBPART_HISTOGRAMS

    USER_SUBPART_HISTOGRAMS

    Display the histogram data (end-points for each histogram) for histograms on table subpartitions.

    DBA_PART_INDEXES

    ALL_PART_INDEXES

    USER_PART_INDEXES

    Display partitioning information for partitioned indexes.

    DBA_IND_PARTITIONS

    ALL_IND_PARTITIONS

    USER_IND_PARTITIONS

    Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.

    DBA_IND_SUBPARTITIONS

    ALL_IND_SUBPARTITIONS

    USER_IND_SUBPARTITIONS

    Display the following for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.


    Viewing Information About Views, Synonyms, and Sequences

    The following views display information about views, synonyms, and sequences: See Example

    View

    Description

    DBA_VIEWS

    ALL_VIEWS

    USER_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

    ALL_SYNONYMS

    USER_SYNONYMS

    These views describe synonyms.

    DBA_SEQUENCES

    ALL_SEQUENCES

    USER_SEQUENCES

    These views describe sequences.

    DBA_UPDATABLE_COLUMNS

    ALL_UPDATABLE_COLUMNS

    USER_UPDATABLE_COLUMNS

    These views describe all columns in join views that are updatable.

    Viewing Constraint Information

    Oracle provides the following views that enable you to see constraint definitions on tables and to identify columns that are specified in constraints:

    View

    Description

    DBA_CONSTRAINTS

    ALL_CONSTRAINTS

    USER_CONSTRAINTS

    DBA view describes all constraint definitions in the database. ALL view describes constraint definitions accessible to current user. USER view describes constraint definitions owned by the current user.

    DBA_CONS_COLUMNS

    ALL_CONS_COLUMNS

    USER_CONS_COLUMNS

    DBA view describes all columns in the database that are specified in constraints. ALL view describes only those columns accessible to current user that are specified in constraints. USER view describes only those columns owned by the current user that are specified in constraints.

    Viewing Information About Database Users and Profiles

    The following data dictionary views contain information about database users and profiles:

    View

    Description

    DBA_USERS

    ALL_USERS

    USER_USERS

    DBA view describes all users of the database. ALL view lists users visible to the current user, but does not describe them. USER view describes only the current user.

    DBA_TS_QUOTAS

    USER_TS_QUOTAS

    Describes tablespace quotas for users.

    USER_PASSWORD_LIMITS

    Describes the password profile parameters that are assigned to the user.

    USER_RESOURCE_LIMITS

    Displays the resource limits for the current user.

    DBA_PROFILES

    Displays all profiles and their limits.

    RESOURCE_COST

    Lists the cost for each resource.

    V$SESSION

    Lists session information for each current session. Includes user name.

    V$SESSTAT

    Lists user session statistics.

    V$STATNAME

    Displays decoded statistic names for the statistics shown in the V$SESSTAT view.

    PROXY_USERS

    Describes users who can assume the identity of other users.

    Viewing Privilege and Role Information

    To access information about grants of privileges and roles, you can query the following data dictionary views:

    View

    Description

    DBA_COL_PRIVS

    ALL_COL_PRIVS

    USER_COL_PRIVS

    DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.

    ALL_COL_PRIVS_MADE

    USER_COL_PRIVS_MADE

    ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.

    ALL_COL_PRIVS_RECD

    USER_COL_PRIVS_RECD

    ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.

    DBA_TAB_PRIVS

    ALL_TAB_PRIVS

    USER_TAB_PRIVS

    DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.

    ALL_TAB_PRIVS_MADE

    USER_TAB_PRIVS_MADE

    ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.

    ALL_TAB_PRIVS_RECD

    USER_TAB_PRIVS_RECD

    ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.

    DBA_ROLES

    This view lists all roles that exist in the database.

    DBA_ROLE_PRIVS

    USER_ROLE_PRIVS

    DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.

    DBA_SYS_PRIVS

    USER_SYS_PRIVS

    DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.

    ROLE_ROLE_PRIVS

    This view describes roles granted to other roles. Information is provided only about roles to which the user has access.

    ROLE_SYS_PRIVS

    This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.

    ROLE_TAB_PRIVS

    This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.

    SESSION_PRIVS

    This view lists the privileges that are currently enabled for the user.

    SESSION_ROLES

    This view lists the roles that are currently enabled to the user.