The following query returns the control file information about the online redo log for a database.

SELECT * FROM V$LOG;

GROUP#  THREAD#    SEQ    BYTES  MEMBERS  ARC STATUS     FIRST_CHANGE# FIRST_TIM
------  -------  -----  -------  -------  --- ---------  ------------- ---------
     1        1  10605  1048576        1  YES ACTIVE          11515628 16-APR-00
     2        1  10606  1048576        1  NO  CURRENT         11517595 16-APR-00
     3        1  10603  1048576        1  YES INACTIVE        11511666 16-APR-00
     4        1  10604  1048576        1  YES INACTIVE        11513647 16-APR-00

To see the names of all of the member of a group, use a query similar to the following:

SELECT * FROM V$LOGFILE;

GROUP#   STATUS  MEMBER
------  -------  ----------------------------------
     1           D:\ORANT\ORADATA\IDDB2\REDO04.LOG
     2           D:\ORANT\ORADATA\IDDB2\REDO03.LOG
     3           D:\ORANT\ORADATA\IDDB2\REDO02.LOG
     4           D:\ORANT\ORADATA\IDDB2\REDO01.LOG

If STATUS is blank for a member, then the file is in use.

For example, the following query displays which online redo log group requires archiving:

SELECT GROUP#, ARCHIVED
   FROM SYS.V$LOG;

GROUP#     ARC
--------   ---
       1   YES
       2   NO

To see the current archiving mode, query the V$DATABASE view:

SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

See Also:

Oracle9i Database Reference for detailed descriptions of data dictionary views

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.

Displaying Information About a Job

The following query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
   FROM DBA_JOBS;

JOB      NEXT_DATE  NEXT_SEC  FAILURES  B
-------  ---------  --------  --------  -
   9125  01-JUN-01  00:00:00         4  N
  14144  24-OCT-01  16:35:35         0  N
   9127  01-JUN-01  00:00:00        16  Y
3 rows selected.

Displaying Information About Running Jobs

You can also display information about only the jobs currently running. The following query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
  FROM DBA_JOBS_RUNNING r, DBA_JOBS j
  WHERE r.JOB = j.JOB;

SID        JOB        LOG_USER      THIS_DATE  THIS_SEC
-----  ----------   -------------   ---------  --------
   12       14144   HR              24-OCT-94  17:21:24
   25        8536   QS              24-OCT-94  16:45:12
2 rows selected.

Listing Tablespaces and Default Storage Parameters: Example

To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

SELECT TABLESPACE_NAME "TABLESPACE",
   INITIAL_EXTENT "INITIAL_EXT",
   NEXT_EXTENT "NEXT_EXT",
   MIN_EXTENTS "MIN_EXT",
   MAX_EXTENTS "MAX_EXT",
   PCT_INCREASE
   FROM DBA_TABLESPACES;

TABLESPACE  INITIAL_EXT  NEXT_EXT  MIN_EXT   MAX_EXT    PCT_INCREASE  
----------  -----------  --------  -------   -------    ------------ 
RBS             1048576   1048576        2        40               0
SYSTEM           106496    106496        1        99               1
TEMP             106496    106496        1        99               0
TESTTBS           57344     16384        2        10               1
USERS             57344     57344        1        99               1

Listing the Datafiles and Associated Tablespaces of a Database: Example

To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

FILE_NAME                                      BLOCKS  TABLESPACE_NAME
------------                               ----------  -------------------
/U02/ORACLE/IDDB3/RBS01.DBF                      1536  RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF                   6586  SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF                     6400  TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF                  6400  TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF                     384  USERS

Displaying Statistics for Free Space (Extents) of Each Tablespace: Example

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
   COUNT(*)    "PIECES",
   MAX(blocks) "MAXIMUM",
   MIN(blocks) "MINIMUM",
   AVG(blocks) "AVERAGE",
   SUM(blocks) "TOTAL"
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE    FILE_ID  PIECES   MAXIMUM    MINIMUM  AVERAGE    TOTAL
----------    -------  ------   -------    -------  -------   ------
RBS                 2       1       955        955      955      955
SYSTEM              1       1       119        119      119      119
TEMP                4       1      6399       6399     6399     6399
TESTTBS             5       5      6364          3     1278     6390
USERS               3       1       363        363      363      363

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

This example illustrates the use of one of these views, V$DATAFILE.

SELECT NAME,
    FILE#,
    STATUS,
    CHECKPOINT_CHANGE# "CHECKPOINT"   
  FROM   V$DATAFILE;

NAME                                      FILE#     STATUS       CHECKPOINT
--------------------------------          -----     -------       ----------
/u01/oracle/rbdb1/system01.dbf                1     SYSTEM              3839
/u02/oracle/rbdb1/temp01.dbf                  2     ONLINE              3782
/u02/oracle/rbdb1/users03.dbf                 3     OFFLINE             3782

FILE# lists the file number of each datafile; the first datafile in the SYSTEM tablespace created with the database is always file 1. STATUS lists other information about a datafile. If a datafile is part of the SYSTEM tablespace, its status is SYSTEM (unless it requires recovery). If a datafile in a non-SYSTEM tablespace is online, its status is ONLINE. If a datafile in a non-SYSTEM tablespace is offline, its status can be either OFFLINE or RECOVER. CHECKPOINT lists the final SCN (system change number) written for a datafile's most recent checkpoint.

Displaying Rollback Segment Information

The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database:

SELECT SEGMENT_NAME, TABLESPACE_NAME, STATUS
        FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME  TABLESPACE_NAME     STATUS
------------- ----------------    ------
SYSTEM        SYSTEM              ONLINE
PUBLIC_RS     SYSTEM              ONLINE
USERS_RS      USERS               ONLINE

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

USER_SEGMENTS
DBA_SEGMENTS

Monitoring Rollback Segment Statistics

The V$ROLLSTAT dynamic performance view can be queried to monitor rollback segment statistics. It must be joined with the V$ROLLNAME view to map its segment number to its name.

Some specific columns of interest in the V$ROLLSTAT view include:

Name Description

USN

Rollback segment number. If this view is joined with the V$ROLLNAME view, the rollback segment name can be determined.

WRITES

The number of bytes of entries written to the rollback segment.

XACTS

The number of active transactions.

GETS

The number of rollback segment header requests.

WAITS

The number of rollback segment header requests that resulted in waits.

OPTSIZE

The value of the optimal parameter for the rollback segment.

HWMSIZE

The highest value (high water mark), in bytes, of the rollback segment size reached during usage.

SHRINKS

The number of shrinks that the rollback segment has had to perform in order to stay at the optimal size.

WRAPS

The number of times a rollback segment entry has wrapped from one extent to another.

EXTENDS

The number of times that the rollback segment had to acquire a new extent.

AVESHRINK

The average number of bytes freed during a shrink.

AVEACTIVE

The average number of bytes in active extents in the rollback segment, measured over time.

These statistics are reset at system startup.

Ad hoc querying of this view can help in determining the most advantageous setting for the OPTIMAL parameter. Assuming that an instance has equally sized rollback segments with comparably sized extents, OPTIMAL for a given rollback segment should be set slightly higher than AVEACTIVE. The following chart provides additional information on how to interpret the statistics given in this view.

SHRINKS AVESHRINK Analysis and Recommendation

Low

Low

If AVEACTIVE is close to OPTSIZE, then the OPTIMAL setting is correct. Otherwise, OPTIMAL is too large (not many shrinks are being performed.)

Low

High

Excellent: a good setting for OPTIMAL.

High

Low

OPTIMAL is too small: too many shrinks are being performed.

High

High

Periodic long transactions are probably causing these statistics. Set the OPTIMAL parameter higher until SHRINKS is low.

Displaying All Rollback Segments

The following query returns the name of each rollback segment, the tablespace that contains it, and its size:

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
   FROM DBA_SEGMENTS
   WHERE SEGMENT_TYPE = 'ROLLBACK';

SEGMENT_NAME   TABLESPACE_NAME    BYTES      BLOCKS     EXTENTS
------------   ---------------    -------    ------     -------
SYSTEM         SYSTEM              409600       200           8
RB_TEMP        SYSTEM             1126400       550          11
RB1            RBS                 614400       300           3
RB2            RBS                 614400       300           3
RB3            RBS                 614400       300           3
RB4            RBS                 614400       300           3
RB5            RBS                 614400       300           3
RB6            RBS                 614400       300           3
RB7            RBS                 614400       300           3
RB8            RBS                 614400       300           3
10 rows selected.

Displaying Whether a Rollback Segment Has Gone Offline

When you take a rollback segment offline, it does not actually go offline until all active transactions in it have completed. Between the time when you attempt to take it offline and when it actually is offline, its status in V$ROLLSTAT is PENDING OFFLINE and it is not used for new transactions. To determine whether any rollback segments for an instance are in this state, use the following query:

SELECT NAME, XACTS "ACTIVE TRANSACTIONS"
      FROM V$ROLLNAME, V$ROLLSTAT
      WHERE STATUS = 'PENDING OFFLINE'
        AND V$ROLLNAME.USN = V$ROLLSTAT.USN;

NAME         ACTIVE TRANSACTIONS
----------   --------------------
RS2                             3
>

If your instance is part of an Oracle Real Application Clusters configuration, this query displays information for rollback segments of the current instance only, not those of other instances.

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: Viewing Information About Database Users and Profiles

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

Viewing Privilege and Role Information

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

Some examples of using these views follow. For these examples, assume the following statements have been issued:

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
   CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
   AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin;

GRANT security_admin, CREATE SESSION TO swilliams;

GRANT security_admin TO system_administrator;

GRANT CREATE SESSION TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;

See Also:

Oracle9i Database Reference for a detailed description of these data dictionary views

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

Listing All Role Grants

The following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO

Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'JWARD';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP          DELETE       NO

To list all the column-specific privileges that have been granted, use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;

If swilliams has enabled the security_admin role and issues this query, Oracle returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

If swilliams has the security_admin role enabled and issues this query, Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the security_admin role is disabled for swilliams, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.

For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT

Name	Description
`USN`	Rollback segment number. If this view is joined with the `V$ROLLNAME` view, the rollback segment name can be determined.
`WRITES`	The number of bytes of entries written to the rollback segment.
`XACTS`	The number of active transactions.
`GETS`	The number of rollback segment header requests.
`WAITS`	The number of rollback segment header requests that resulted in waits.
`OPTSIZE`	The value of the optimal parameter for the rollback segment.
`HWMSIZE`	The highest value (high water mark), in bytes, of the rollback segment size reached during usage.
`SHRINKS`	The number of shrinks that the rollback segment has had to perform in order to stay at the optimal size.
`WRAPS`	The number of times a rollback segment entry has wrapped from one extent to another.
`EXTENDS`	The number of times that the rollback segment had to acquire a new extent.
`AVESHRINK`	The average number of bytes freed during a shrink.
`AVEACTIVE`	The average number of bytes in active extents in the rollback segment, measured over time.

SHRINKS	AVESHRINK	Analysis and Recommendation
Low	Low	If `AVEACTIVE` is close to `OPTSIZE`, then the `OPTIMAL` setting is correct. Otherwise, `OPTIMAL` is too large (not many shrinks are being performed.)
Low	High	Excellent: a good setting for `OPTIMAL`.
High	Low	`OPTIMAL` is too small: too many shrinks are being performed.
High	High	Periodic long transactions are probably causing these statistics. Set the `OPTIMAL` parameter higher until `SHRINKS` is low.