Wednesday, August 6, 2008

DBA_USERS

Oracle 11gR1

DBA_USERS describes all users of the database.

Related View

USER_USERS describes the current user. This view does not display the PASSWORD, PROFILE, PASSWORD_VERSIONS, or EDITIONS_ENABLED columns.

Column

Datatype

NULL

Description

USERNAME

VARCHAR2(30)

NOT NULL

Name of the user

USER_ID

NUMBER

NOT NULL

ID number of the user

PASSWORD

VARCHAR2(30)

Encrypted password

ACCOUNT_STATUS

VARCHAR2(32)

NOT NULL

Account status:

· OPEN

· EXPIRED

· EXPIRED(GRACE)

· LOCKED(TIMED)

· LOCKED

· EXPIRED & LOCKED(TIMED)

· EXPIRED(GRACE) & LOCKED(TIMED)

· EXPIRED & LOCKED

· EXPIRED(GRACE) & LOCKED

LOCK_DATE

DATE

Date the account was locked if account status was LOCKED

EXPIRY_DATE

DATE

Date of expiration of the account

DEFAULT_TABLESPACE

VARCHAR2(30)

NOT NULL

Default tablespace for data

TEMPORARY_TABLESPACE

VARCHAR2(30)

NOT NULL

Name of the default tablespace for temporary tables or the name of a tablespace group

CREATED

DATE

NOT NULL

User creation date

PROFILE

VARCHAR2(30)

NOT NULL

User resource profile name

INITIAL_RSRC_CONSUMER_GROUP

VARCHAR2(30)

Initial resource consumer group for the user

EXTERNAL_NAME

VARCHAR2(4000)

User external name

PASSWORD_VERSIONS

VARCHAR2(8)

Database version in which the password was created or changed

EDITIONS_ENABLED

VARCHAR2(1)

Reserved for future use

Oracle data dictionary views

DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

Column
Datatype
NULL
Description
SEGMENT_NAME
VARCHAR2(30)
NOT NULL
Name of the rollback segment
OWNER
VARCHAR2(6)

Owner of the rollback segment
TABLESPACE_NAME
VARCHAR2(30)
NOT NULL
Name of the tablespace containing the rollback segment
SEGMENT_ID
NUMBER
NOT NULL
ID number of the rollback segment
FILE_ID
NUMBER
NOT NULL
File identifier number of the file containing the segment head
BLOCK_ID
NUMBER
NOT NULL
ID number of the block containing the segment header
INITIAL_EXTENT
NUMBER

Initial extent size in bytes
NEXT_EXTENT
NUMBER

Secondary extent size in bytes
MIN_EXTENTS
NUMBER
NOT NULL
Minimum number of extents
MAX_EXTENTS
NUMBER
NOT NULL
Maximum number of extent
PCT_INCREASE
NUMBER

Percent increase for extent size
STATUS
VARCHAR2(16)

Rollback segment status
INSTANCE_NUM
VARCHAR2(40)

Rollback segment owning Real Application Clusters instance number
RELATIVE_FNO
NUMBER
NOT NULL
Relative file number of the segment header

Note:
1.       In automatic undo management (AUM) DBA's don't have privileges to offline/online undo segments. And this is controlled by SMON process. So the view will be useful only in few scenarios, where we have internal errors with undo segments.

2.       Scripts using DBA_ROLLBACK_SEGS
-- All Rollback Segments

select segment_name, owner, tablespace_name, name
from   dba_rollback_segs d, v$datafile f
where  d.file_id = f.file#;
-- Online Rollback Segments

select d.segment_name, s.extents,
       round((s.rssize/1024/1024))||'M',
       d.initial_extent, d.next_extent,
       d.pct_increase, d.min_extents,
       d.max_extents, optsize, d.status
from   v$rollname n, v$rollstat s, dba_rollback_segs d
where  n.usn = s.usn
and    d.segment_name = n.name(+);
 


Oracle data dictionary views

Last updated: 2009-10-27 Tuesday

V$ARCHIVED_LOG

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

========================================================================

V$ARCHIVED_LOG displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

Column
Datatype
Description
RECID
NUMBER
Archived log record ID
STAMP
NUMBER
Archived log record stamp
NAME
VARCHAR2(513)
Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).
DEST_ID
NUMBER
Original destination from which the archive log was generated. The value is 0 if the destination identifier is not available.
THREAD#
NUMBER
Redo thread number
SEQUENCE#
NUMBER
Redo log sequence number
RESETLOGS_CHANGE#
NUMBER
Resetlogs change number of the database when the log was written
RESETLOGS_TIME
DATE
Resetlogs time of the database when the log was written
RESETLOGS_ID
NUMBER
Resetlogs identifier associated with the archived redo log
FIRST_CHANGE#
NUMBER
First change number in the archived log
FIRST_TIME
DATE
Timestamp of the first change
NEXT_CHANGE#
NUMBER
First change in the next log
NEXT_TIME
DATE
Timestamp of the next change
BLOCKS
NUMBER
Size of the archived log (in blocks)
BLOCK_SIZE
NUMBER
Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.
CREATOR
VARCHAR2(7)
Creator of the archivelog:
·         ARCH - Archiver process
·         FGRD - Foreground process
·         RMAN - Recovery Manager
·         SRMN - RMAN at standby
·         LGWR - Logwriter process
REGISTRAR
VARCHAR2(7)
Registrar of the entry:
·         RFS - Remote File Server process
·         ARCH - Archiver process
·         FGRD - Foreground process
·         RMAN - Recovery manager
·         SRMN - RMAN at standby
·         LGWR - Logwriter process
STANDBY_DEST
VARCHAR2(3)
Indicates whether the entry is an archivelog destination (YES) or not (NO)
ARCHIVED
VARCHAR2(3)
Indicates whether the online redo log was archived (YES) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (NO).
APPLIED
VARCHAR2(3)
Indicates whether the archivelog has been applied to its corresponding standby database (YES) or not (NO). The value is always NO for local destinations.
This column is meaningful at the physical standby site for the ARCHIVED_LOG entries with REGISTRAR='RFS' (which means this log is shipped from the primary to the standby database). If REGISTRAR='RFS' and APPLIED is NO, then the log has arrived at the standby but has not yet been applied. If REGISTRAR='RFS' and APPLIED is YES, the log has arrived and been applied at the standby database.
You can use this field to identify archivelogs that can be backed up and removed from disk.
DELETED
VARCHAR2(3)
Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (YES) or not (NO)
STATUS
VARCHAR2(1)
Status of the archived log:
A - Available
D - Deleted
U - Unavailable
X - Expired
COMPLETION_TIME
DATE
Time when the archiving completed
DICTIONARY_BEGIN
VARCHAR2(3)
Indicates whether the log contains the start of a LogMiner dictionary (YES) or not (NO)
DICTIONARY_END
VARCHAR2(3)
Indicates whether the log contains the end of a LogMiner dictionary (YES) or not (NO)
END_OF_REDO
VARCHAR2(3)
Indicates whether the archived redo log contains the end of all redo information from the primary database (YES) or not (NO)
BACKUP_COUNT
NUMBER
Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15.
ARCHIVAL_THREAD#
NUMBER
Redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance.
ACTIVATION#
NUMBER
Number assigned to the database instantiation
IS_RECOVERY_DEST_FILE
VARCHAR2(3)
Indicates whether the file was created in the flash recovery area (YES) or not (NO)
COMPRESSED
VARCHAR2(3)
This column is reserved for internal use only
FAL
VARCHAR2(3)
Indicates whether the archive log was generated as the result of a FAL request (YES) or not (NO)
END_OF_REDO_TYPE
VARCHAR2(10)
Possible values are as follows:
·         SWITCHOVER - Shows archived redo log files that are produced at the end of a switchover
·         TERMINAL - Shows archived redo log files produced after a failover
·         RESETLOGS - Shows online redo log files archived on the primary database after an ALTER DATABASE OPEN RESETLOGS statement is issued
·         ACTIVATION - Shows any log files archived on a physical standby database after an ALTER DATABASE ACTIVATE STANDBY DATABASE statement is issued
·         "empty string" - Any empty string implies that that the log is just a normal archival and was not archived due to any of the other events
BACKED_BY_VSS
VARCHAR2(3)
Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is for internal use only.
Note:
  1. V$ARCHIVED_LOG replaces V$LOG_HISTORY in Oracle8.
  1. How to Delete All Archived Log Entries in the V$ARCHIVED_LOG?
The number of archived redo log files maintained in the controlfile and available through V$ARCHIVED_LOG is determined by the MAXLOGHISTORY parameter of the create database command. The archived redo log records are maintained up to the value of MAXLOGHISTORY, then wrap and overlay previous entries starting from the beginning.  Eventually, all entries in V$ARCHIVED_LOG will be overwritten after the number of archived logs exceeds MAXLOGHISTORY. Recreate the controlfile to force the removal of all archived log entries from the controlfile and the V$ARCHIVED_LOG view.
  1. Script to check when archived redo logs are applied
select sequence#, name, to_char(first_time,'mm-dd-yyyy hh24:mi:ss') first_time,
       to_char(completion_time,'mm-dd-yyyy hh24:mi:ss') end_time,
       archived, creator, registrar, status, deleted, standby_dest, applied
from   v$archived_log
where  first_time > sysdate -1
order by 3;
4.  The following RMAN example shows how to backup all archived redo logs with a sequence number of 234 or higher to a backupset. After the archived redo logs are copied, they are deleted from disk and marked as deleted in the V$ARCHIVED_LOG.
RMAN> BACKUP
      FORMAT '/disk1/backup/ar_%t_%s_%p'
      ARCHIVELOG FROM SEQUENCE=234
      DELETE INPUT

Oracle dynamic performance views

Last updated: September 16, 2009 Wednesday