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:

SQL> ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\ORANT\oradata\IDDB2\archive
Oldest online log sequence     11160
Next log sequence to archive   11163
Current log sequence           11163

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.

  My Oracle Debug Guide