V$TRANSACTION

V$TRANSACTION lists the active transactions in the system.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of the transaction state object
XIDUSN	NUMBER	Undo segment number
XIDSLOT	NUMBER	Slot number
XIDSQN	NUMBER	Sequence number
UBAFIL	NUMBER	Undo block address (UBA) filenum
UBABLK	NUMBER	UBA block number
UBASQN	NUMBER	UBA sequence number
UBAREC	NUMBER	UBA record number
STATUS	VARCHAR2(16)	Status
START_TIME	VARCHAR2(20)	Start time (wall clock)
START_SCNB	NUMBER	Start system change number (SCN) base
START_SCNW	NUMBER	Start SCN wrap
START_UEXT	NUMBER	Start extent number
START_UBAFIL	NUMBER	Start UBA file number
START_UBABLK	NUMBER	Start UBA block number
START_UBASQN	NUMBER	Start UBA sequence number
START_UBAREC	NUMBER	Start UBA record number
SES_ADDR	RAW(4 | 8)	User session object address
FLAG	NUMBER	Flag
SPACE	VARCHAR2(3)	YES if a space transaction
RECURSIVE	VARCHAR2(3)	YES if a recursive transaction
NOUNDO	VARCHAR2(3)	YES if a no undo transaction
PTX	VARCHAR 2(3)	YES if parallel transaction
NAME	VARCHAR2(256)	Name of a named transaction
PRV_XIDUSN	NUMBER	Previous transaction undo segment number
PRV_XIDSLT	NUMBER	Previous transaction slot number
PRV_XIDSQN	NUMBER	Previous transaction sequence number
PTX_XIDUSN	NUMBER	Rollback segment number of the parent XID
PTX_XIDSLT	NUMBER	Slot number of the parent XID
PTX_XIDSQN	NUMBER	Sequence number of the parent XID
DSCN-B	NUMBER	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE.
DSCN-W	NUMBER	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP.
USED_UBLK	NUMBER	Number of undo blocks used
USED_UREC	NUMBER	Number of undo records used
LOG_IO	NUMBER	Logical I/O
PHY_IO	NUMBER	Physical I/O
CR_GET	NUMBER	Consistent gets
CR_CHANGE	NUMBER	Consistent changes
START_DATE	DATE	Start time (wall clock)
DSCN_BASE	NUMBER	Dependent SCN base
DSCN_WRAP	NUMBER	Dependent SCN wrap
START_SCN	NUMBER	Start SCN
DEPENDENT_SCN	NUMBER	Dependent SCN
XID	RAW(8)	Transaction XID
PRV_XID	RAW(8)	Previous transaction XID
PTX_XID	RAW(8)	Parent transaction XID

Note:

1. The following columns together give unique transaction id:

XIDUSN - Undo segment number XIDSLOT - NUMBER Slot number XIDSQN - NUMBER Sequence number

2. The following columns explain the number of UNDO blocks / UNDO records used per transaction:

USED_UBLK - Number of undo blocks used USED_UREC - Number of undo records used

These two columns will give estimation about the number of undo blocks that need to be rolled back in the case of transaction rollback. When the values decrease to 0, the transaction disappears from V$TRANSACTION. To monitor the rollback, use script

SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK  FROM   V$SESSION A, V$TRANSACTION B  WHERE  A.SADDR=B.SES_ADDR;

3. STATUS column explains the status of the transaction. Before a normal/transactional shutdown, check the view to see if there is any ACTIVE transactions:

ACTIVE – the transaction is active.

SELECT XIDUSN, XIDSLOT, XIDSQN, SES_ADDR, STATUS FROM   V$TRANSACTION;

4. Before issuing shutdown immediate, it would be recommended to check the following views, especially when the database needs to be brought down for a very short period of time:

For large queries:

        select count(*) from v$session_longops where time_remaining>0;

For large transactions:

        select sum(used_ublk) from v$transaction;

A result greater than 0 for the first query and a large value returned for the second one would mean a relatively long time to wait until the shutdown immediate completes.

5. Script to show current running transactions

select a.username, a.program, c.name as rbsname,        b.status, b.phy_io from   V$SESSION a, v$transaction b, v$rollname c where  a.saddr = b.ses_addr and b.xidusn = c.usn and b.phy_io > 0 order by 5 desc;

6. Use V$TRANSACTION to find sessions generating lots of redo:

V$TRANSACTION contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns). SELECT s.sid, s.serial#, s.username,        s.program, t.used_ublk, t.used_urec FROM   V$SESSION s, v$transaction t WHERE  s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4; Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-29 Thursday

V$TEMP_EXTENT_MAP

Oracle 11gR1

V$TEMP_EXTENT_MAP displays the status of each unit for all LOCALLY MANAGED temporary tablespaces.

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace this unit belongs to
FILE_ID	NUMBER	Absolute file number
BLOCK_ID	NUMBER	Begin block number for this unit
BYTES	NUMBER	Bytes in the extent
BLOCKS	NUMBER	Blocks in the extent
OWNER	NUMBER	Instance which owns this unit
RELATIVE_FNO	NUMBER	Relative file number

Note:

1.

select * from V$TEMP_EXTENT_MAP;

TABLESPACE_NAME	FILE_ID	BLOCK_ID	BYTES	BLOCKS	OWNER	RELATIVE_FNO
TEMP	8	5	1048576	64	1	8
TEMP	8	69	1048576	64	1	8
TEMP	8	133	1048576	64	1	8
TEMP	8	197	1048576	64	1	8

Oracle data dictionary views

V$TEMP_EXTENT_POOL

Oracle 11gR1

V$TEMP_EXTENT_POOL displays the state of temporary space cached and used for the instance. Note that loading of the temporary space cache is lazy and that instances can be dormant.

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace
FILE_ID	NUMBER	Absolute file number
EXTENTS_CACHED	NUMBER	Number of extents that have been cached
EXTENTS_USED	NUMBER	Number of extents that are actually being used
BLOCKS_CACHED	NUMBER	Number of blocks that are cached
BLOCKS_USED	NUMBER	Number of blocks that are used
BYTES_CACHED	NUMBER	Number of bytes that are cached
BYTES_USED	NUMBER	Number of bytes that are used
RELATIVE_FNO	NUMBER	Relative file number

Note:

1.

select * from V$TEMP_EXTENT_POOL;

TABLESPACE _NAME	FILE _ID	EXTENTS _CACHED	EXTENTS _USED	BLOCKS _CACHED	BLOCKS _USED	BYTES_CACHED	BYTES_USED	RELATIVE_FNO
TEMP	1	936	0	59904	0	981467136	0	1
TEMP	2	924	0	59136	0	968884224	0	2
TEMP	3	871	0	55744	0	913309696	0	3
TEMP	4	790	0	50560	0	828375040	0	4
TEMP	5	807	106	51648	6784	846200832	111149056	5

Oracle data dictionary views

V$ENCRYPTED_TABLESPACES

Oracle 11gR1

V$ENCRYPTED_TABLESPACES displays information about the tablespaces that are encrypted.

Column	Datatype	Description
TS#	NUMBER	Tablespace number
ENCRYPTIONALG	VARCHAR2(7)	Encryption algorithm: · NONE · 3DES168 · AES128 · AES192 · AES256
ENCRYPTEDTS	VARCHAR2(3)	Indicates whether the tablespace is encrypted (YES) or not (NO)

Oracle dynamic performance views

V$TABLESPACE

Oracle 11gR1

V$TABLESPACE displays tablespace information from the control file.

Column	Datatype	Description
TS#	NUMBER	Tablespace number
NAME	VARCHAR2(30)	Tablespace name
INCLUDED_IN_DATABASE_BACKUP	VARCHAR2(3)	Indicates whether the tablespace is included in full database backups using the BACKUP DATABASE RMAN command (YES) or not (NO); NO only if the CONFIGURE EXCLUDE RMAN command was used for this tablespace
BIGFILE	VARCHAR2(3)	Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO)
FLASHBACK_ON	VARCHAR2(3)	Indicates whether the tablespace participates in FLASHBACK DATABASE operations (YES) or not (NO)
ENCRYPT_IN_BACKUP	VARCHAR2(3)	Indicates whether encryption is turned ON or off at the tablespace level: · ON - Encryption is turned ON at the tablespace level · OFF - Encryption is turned OFF at the tablespace level · NULL - Encryption is neither explicitly turned on nor off at the tablespace level (default or when cleared)

Note:

1.

select * from V$TABLESPACE;

TS#	NAME	INCLUDED_IN_DATABASE_BACKUP	BIGFILE	FLASHBACK_ON	ENCRYPT_IN_BACKUP
0	SYSTEM	YES	NO	YES	NULL
2	TEMP	NO	NO	YES	NULL
3	CWMLITE	YES	NO	YES	NULL
6	TOOLS	YES	NO	YES	NULL
7	USERS	YES	NO	YES	NULL

Oracle dynamic performance views

V$BACKUP

V$BACKUP displays the backup status of all online datafiles.

Column	Datatype	Description
FILE#	NUMBER	File identifier
STATUS	VARCHAR2(18)	File status: NOT ACTIVE, ACTIVE (backup in progress), OFFLINE NORMAL, or description of an error. NOT ACTIVE indicates that the file is not currently in backup mode (that is, an ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP statement has not been issued), whereas ACTIVE indicates that the file is currently in backup mode.
CHANGE#	NUMBER	System change number when backup started
TIME	DATE	Time the backup started

Note:

1.

select d.name, b.* from v$backup b, v$datafile d where b.file#=d.file#;

NAME	FILE#	STATUS	CHANGE#	TIME
/oracle_data/mydb/system01.dbf	1	NOT ACTIVE	2210368887096	2005-09-14 12:48:57 PM
/oracle_data/mydb/undo25.dbf	2	NOT ACTIVE	0	NULL
/oracle_data/mydb/sde_logfile.dbf	3	NOT ACTIVE	0	NULL
/oracle_data/mydb/tools01.dbf	7	NOT ACTIVE	2210369026684	2005-09-14 2:41:08 PM
/oracle_data/mydb/users01.dbf	8	NOT ACTIVE	2210369651966	2005-09-14 7:25:53 PM

Oracle data dictionary views

Oracle dynamic performance views

V$RECOVER_FILE

Oracle 11gR1

V$RECOVER_FILE displays the status of files needing media recovery.

Column	Datatype	Description
FILE#	NUMBER	File identifier number
ONLINE	VARCHAR2(7)	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in ONLINE_STATUS.
ONLINE_STATUS	VARCHAR2(7)	Online status (ONLINE, OFFLINE)
ERROR	VARCHAR2(18)	Why the file needs to be recovered: NULL if reason unknown, or OFFLINE NORMAL if recovery not needed
CHANGE#	NUMBER	SCN where recovery must start
TIME	DATE	Time of SCN when recovery must start

Note:

Oracle dynamic performance views