V$PROCESS

V$PROCESS displays information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of the process state object
PID	NUMBER	Oracle process identifier
SPID	VARCHAR2(24)	Operating system process identifier
USERNAME	VARCHAR2(15)	Operating system process username Note: Any two-task user coming across the network has "-T" appended to the username.
SERIAL#	NUMBER	Process serial number
TERMINAL	VARCHAR2(30)	Operating system terminal identifier
PROGRAM	VARCHAR2(48)	Program in progress
TRACEID	VARCHAR2(255)	Trace file identifier
TRACEFILE	VARCHAR2(513)	Trace file name of the process
BACKGROUND	VARCHAR2(1)	1 for a background process; NULL for a normal process
LATCHWAIT	VARCHAR2(8)	Address of the latch the process is waiting for; NULL if none
LATCHSPIN	VARCHAR2(8)	Address of the latch the process is spinning on; NULL if none
PGA_USED_MEM	NUMBER	PGA memory currently used by the process
PGA_ALLOC_MEM	NUMBER	PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_FREEABLE_MEM	NUMBER	Allocated PGA memory which can be freed
PGA_MAX_MEM	NUMBER	Maximum PGA memory ever allocated by the process

Note:

1.       Three columns in the view report the PGA memory allocated and used by an Oracle process: PGA_USED_MEM, PGA_ALLOCATED_MEM, PGA_MAX_MEM.

2.       Scripts using V$PROCESS

--  PGA usage select program, pga_used_mem/1024/1024, pga_alloc_mem/1024/1024,        PGA_FREEABLE_MEM/1024/1024, pga_max_mem/1024/1024 from   v$process order by 5 desc;

-- monitor current user activity in the database by generating a list of the SQL statement being executed in the database select c.spid, b.osuser, b.username, b.sid, b.serial#,        a.sql_text   from v$sqltext a, v$session b, v$process c  where a.address = b.sql_address -- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */    and b.paddr = c.addr    and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece

-- obtain session information select 'Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||        s.audsid||chr(10)|| 'DB User / OS User : '||s.username||        '   /   '||s.osuser||chr(10)|| 'Machine - Terminal : '||        s.machine||'  -  '|| s.terminal||chr(10)||        ' OS Process Ids : '||        s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||        'Client Program Name : '||s.program "Session Info"   from v$process p, v$session s  where p.addr = s.paddr    and s.audsid = userenv('SESSIONID')

-- list inactive users to ensure whether to kill a session select s.username||' '||s.osuser userinfo, s.terminal, s.sid, s.serial#,        p.spid, s.process , w.seq# from   v$session s, v$process p, v$session_wait w where  p.addr = s.paddr and    s.sid = w.sid and    w.event = 'SQL*Net message from client' and    s.status = 'INACTIVE' order by s.osuser, s.terminal;

-- script to map connected clients to their associated shadow server process select     p.spid SHADOW_ID,             s.server SHADOW_TYPE,            s.machine CLIENT_NODE,            s.process CLIENT_ID,            s.osuser CLIENT_ACCOUNT,            s.program CLIENT_PROGRAM     from   v$session s, v$process p     where  p.addr = s.paddr     order by p.spid;

3.       Useful join column: V$PROCESS.ADDR join to V$SESSION.PADDR

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-23 Friday

V$LOG

V$LOG displays log file information from the control file.

Column	Datatype	Description
GROUP#	NUMBER	Log group number
THREAD#	NUMBER	Log thread number
SEQUENCE#	NUMBER	Log sequence number
BYTES	NUMBER	Size of the log (in bytes)
MEMBERS	NUMBER	Number of members in the log group
ARCHIVED	VARCHAR2(3)	Archive status (YES) or (NO)
STATUS	VARCHAR2(16)	Log status: ·         UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log. ·         CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed. ·         ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. ·         CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED. ·         CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. ·         INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
FIRST_CHANGE#	NUMBER	Lowest system change number (SCN) in the log
FIRST_TIME	DATE	Time of the first SCN in the log

Note:

1.       Scripts using V$LOG

-- show redo log information select group#, thread#, sequence#, (bytes/power(10,6)) MB,        members, archived, status, first_change#,        to_char(first_time, 'DDMonYY hh24:mi') first_time from   V$LOG order by first_time; select  l.group#, member, archived, l.status, (bytes/1024/1024) fsize from    v$log l, v$logfile f where   f.group# = l.group# order by 1

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-23 Friday

DBA_RECYCLEBIN

Oracle 11gR1

DBA_RECYCLEBIN displays information about all recycle bins in the database.

Related View

USER_RECYCLEBIN displays information about the recycle bin owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Name of the original owner of the object
OBJECT_NAME	VARCHAR2(30)	NOT NULL	New name of the object
ORIGINAL_NAME	VARCHAR2(32)		Original name of the object
OPERATION	VARCHAR2(9)		Operation carried out on the object: · DROP - Object was dropped · TRUNCATE - Object was truncated Note: The Oracle Database currently only supports recovering dropped objects from the recycle bin. The truncated objects cannot be recovered.
TYPE	VARCHAR2(25)		Type of the object: · TABLE · NORMAL INDEX · BITMAP INDEX · NESTED TABLE · LOB · LOB INDEX · DOMAIN INDEX · IOT TOP INDEX · IOT OVERFLOW SEGMENT · IOT MAPPING TABLE · TRIGGER · CONSTRAINT · Table Partition · Table Composite Partition · Index Partition · Index Composite Partition · LOB Partition · LOB Composite Partition
TS_NAME	VARCHAR2(30)		Name of the tablespace to which the object belongs
CREATETIME	VARCHAR2(19)		Timestamp for the creation of the object
DROPTIME	VARCHAR2(19)		Timestamp for the dropping of the object
DROPSCN	NUMBER		System change number (SCN) of the transaction which moved the object to the recycle bin
PARTITION_NAME	VARCHAR2(32)		Name of the partition which was dropped
CAN_UNDROP	VARCHAR2(3)		Indicates whether the object can be undropped (YES) or not (NO)
CAN_PURGE	VARCHAR2(3)		Indicates whether the object can be purged (YES) or not (NO)
RELATED	NUMBER	NOT NULL	Object number of the parent object
BASE_OBJECT	NUMBER	NOT NULL	Object number of the base object
PURGE_OBJECT	NUMBER	NOT NULL	Object number for the object which gets purged
SPACE	NUMBER		Number of blocks used by the object

DBA_CONSTRAINTS

Oracle 11gR1

DBA_CONSTRAINTS describes all constraint definitions on all tables in the database.

Related Views

· ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

· USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the constraint definition
CONSTRAINT_NAME	VARCHAR2(30)	NOT NULL	Name of the constraint definition
CONSTRAINT_TYPE	VARCHAR2(1)		Type of constraint definition: · C (check constraint on a table) · P (primary key) · U (unique key) · R (referential integrity) · V (with check option, on a view) · O (with read only, on a view)
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name associated with the table (or view) with constraint definition
SEARCH_CONDITION	LONG		Text of search condition for a check constraint
R_OWNER	VARCHAR2(30)		Owner of table referred to in a referential constraint
R_CONSTRAINT_NAME	VARCHAR2(30)		Name of the unique constraint definition for referenced table
DELETE_RULE	VARCHAR2(9)		Delete rule for a referential constraint (CASCADE or NO ACTION)
STATUS	VARCHAR2(8)		Enforcement status of constraint (ENABLED or DISABLED)
DEFERRABLE	VARCHAR2(14)		Whether the constraint is deferrable
DEFERRED	VARCHAR2(9)		Whether the constraint was initially deferred
VALIDATED	VARCHAR2(13)		Whether all data obeys the constraint (VALIDATED or NOT VALIDATED)
GENERATED	VARCHAR2(14)		Whether the name of the constraint is user or system generated
BAD	VARCHAR2(3)		A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year. See Also: the TO_DATE function in Oracle Database SQL Language Reference and Oracle Database Advanced Application Developer's Guide
RELY	VARCHAR2(4)		Whether an enabled constraint is enforced or unenforced. See Also: the constraints in Oracle Database SQL Language Reference
LAST_CHANGE	DATE		When the constraint was last enabled or disabled
INDEX_OWNER	VARCHAR2(30)		Name of the user owning the index
INDEX_NAME	VARCHAR2(30)		Name of the index (only shown for unique and primary-key constraints)
INVALID	VARCHAR2(7)		Whether the constraint is invalid
VIEW_RELATED	VARCHAR2(14)		Whether the constraint depends on a view