Oracle for Beginners to Expert

Saturday, October 25, 2014

List of Oracle Wait Events 11.2.0.3

ADR block file read
ADR block file write
ADR file lock
AQ propagation connection
AQ spill debug idle
ARCH wait for archivelog lock
ARCH wait for flow-control
ARCH wait for net re-connect
ARCH wait for netserver detach
ARCH wait for netserver init 2
ARCH wait for netserver start
ARCH wait for process death 1
ARCH wait for process start 1
ARCH wait for process start 3
ARCH wait on ATTACH
ARCH wait on DETACH
ARCH wait on SENDREQ
ARCH wait on c/f tx acquire 1
ASM COD rollback operation completion
ASM DG Unblock
ASM Fixed Package I/O
ASM Instance startup
ASM PST operation
ASM PST query : wait for [PM][grp][0] grant
ASM Staleness File I/O
ASM Volume Background
ASM background starting
ASM background timer
ASM cluster file access
ASM db client exists
ASM file metadata operation
ASM internal hang test
ASM metadata cache frozen
ASM mount : wait for heartbeat
ASM network foreground exits
ASM: MARK subscribe to msg channel
ASM: OFS Cluster membership update
AWR Flush
AWR Metric Capture
Archive Manager file transfer I/O
Archiver slave I/O
Auto BMR RPC standby catchup
Auto BMR completion
BFILE check if exists
BFILE check if open
BFILE closure
BFILE get length
BFILE get name object
BFILE get path object
BFILE internal seek
BFILE open
BFILE read
Backup Restore Event 19778 sleep
Backup Restore Switch Bitmap sleep
Backup Restore Throttle sleep
Backup: MML command to channel
Backup: MML commit backup piece
Backup: MML create a backup piece
Backup: MML data movement done?
Backup: MML datafile proxy backup?
Backup: MML datafile proxy restore?
Backup: MML delete backup piece
Backup: MML extended initialization
Backup: MML get base address
Backup: MML initialization
Backup: MML obtain a direct buffer
Backup: MML obtain textual error
Backup: MML proxy cancel
Backup: MML proxy commit backup piece
Backup: MML proxy initialize backup
Backup: MML proxy initialize restore
Backup: MML proxy prepare to start
Backup: MML proxy session end
Backup: MML proxy start data movement
Backup: MML query backup piece
Backup: MML query for direct buffers
Backup: MML read backup piece
Backup: MML release a direct buffer
Backup: MML restore backup piece
Backup: MML shutdown
Backup: MML v1 close backup piece
Backup: MML v1 delete backup piece
Backup: MML v1 open backup piece
Backup: MML v1 query backup piece
Backup: MML v1 read backup piece
Backup: MML v1 write backup piece
Backup: MML write backup piece
CGS skgxn join retry
CGS wait for IPC msg
CRS call completion
CSS Xgrp shared operation
CSS group membership query
CSS group registration
CSS initialization
CSS operation: action
CSS operation: data query
CSS operation: data update
CSS operation: diagnostic
CSS operation: query
CTWR media recovery checkpoint request
Clonedb bitmap file write
Cluster Suspension wait
Cluster stabilization wait
Compression analysis
DBMS_LDAP: LDAP operation
DBWR range invalidation sync
DBWR slave I/O
DFS db file lock
DFS lock handle
DG Broker configuration file I/O
DIAG idle wait
DSKM to complete cell health check
Data Guard Broker Wait
Data Guard: RFS disk I/O
Data Guard: process clean up
Data Guard: process exit
Data Pump slave init
Data Pump slave startup
Data file init write
Datapump dump file I/O
Disk file I/O Calibration
Disk file Mirror Read
Disk file Mirror/Media Repair Write
Disk file operations I/O
EMON slave idle wait
EMON slave messages
EMON termination
External Procedure call
External Procedure initial connection
FAL archive wait 1 sec for REOPEN minimum
File Repopulation Write
GCR CSS join retry
GCR ctx lock acquisition
GCR lock acquisition
GCR member Data from CSS
GCR sleep
GCS lock cancel
GCS lock cvt S
GCS lock cvt X
GCS lock esc
GCS lock esc X
GCS lock open
GCS lock open S
GCS lock open X
GCS recovery lock convert
GCS recovery lock open
GIPC operation: dump
GPnP Get Error
GPnP Get Item
GPnP Initialization
GPnP Set Item
GPnP Termination
GV$: slave acquisition retry wait time
Global transaction acquire instance locks
HS message to agent
IMR CSS join retry
IMR disk votes
IMR membership resolution
IMR mount phase II completion
IMR net-check message ack
IMR rr lock release
IMR rr update
IORM Scheduler Slave Idle Wait
IPC busy async request
IPC send completion sync
IPC wait for name service busy
IPC waiting for OSD resources
Image redo gen delay
JOX Jit Process Sleep
JS coord start wait
JS external job
JS kgl get object wait
JS kill job wait
KJC: Wait for msg sends to complete
KSV master wait
Kupp process shutdown
L1 validation
LGWR ORL/NoExp FAL archival
LGWR real time apply sync
LGWR simulation latency wait
LGWR slave I/O
LGWR wait for redo copy
LGWR wait on ATTACH
LGWR wait on DETACH
LGWR wait on LNS
LGWR wait on SENDREQ
LGWR-LNS wait on channel
LMON global data update
LNS ASYNC archive log
LNS ASYNC dest activation
LNS ASYNC end of log
LNS simulation latency wait
LNS wait on ATTACH
LNS wait on DETACH
LNS wait on LGWR
LNS wait on SENDREQ
Log archive I/O
Log file init write
LogMiner builder: DDL
LogMiner builder: branch
LogMiner builder: idle
LogMiner builder: memory
LogMiner client: transaction
LogMiner preparer: idle
LogMiner preparer: memory
LogMiner reader: buffer
LogMiner reader: log (idle)
LogMiner reader: redo (idle)
LogMiner: activate
LogMiner: find session
LogMiner: internal
LogMiner: other
LogMiner: reset
Logical Standby Apply Delay
Logical Standby Apply shutdown
Logical Standby Debug
Logical Standby Terminal Apply
Logical Standby dictionary build
Logical Standby pin transaction
MMON (Lite) shutdown
MMON slave messages
MRP inactivation
MRP redo arrival
MRP state inspection
MRP termination
MRP wait on archivelog archival
MRP wait on archivelog arrival
MRP wait on process restart
MRP wait on process start
MRP wait on startup clear
NFS read delegation outstanding
Network file transfer
OJVM: Generic
OLAP Aggregate Client Deq
OLAP Aggregate Client Enq
OLAP Aggregate Master Deq
OLAP Aggregate Master Enq
OLAP DML Sleep
OLAP Null PQ Reason
OLAP Parallel Temp Grew
OLAP Parallel Temp Grow Request
OLAP Parallel Temp Grow Wait
OLAP Parallel Type Deq
OSD IPC library
PING
PL/SQL lock timer
PMON to cleanup detached branches at shutdown
PMON to cleanup pseudo-branches at svc stop time
PX Deq Credit: Session Stats
PX Deq Credit: free buffer
PX Deq Credit: need buffer
PX Deq Credit: send blkd
PX Deq: Execute Reply
PX Deq: Execution Msg
PX Deq: Index Merge Close
PX Deq: Index Merge Execute
PX Deq: Index Merge Reply
PX Deq: Join ACK
PX Deq: Metadata Update
PX Deq: Msg Fragment
PX Deq: OLAP Update Close
PX Deq: OLAP Update Execute
PX Deq: OLAP Update Reply
PX Deq: Parse Reply
PX Deq: Signal ACK EXT
PX Deq: Signal ACK RSG
PX Deq: Slave Join Frag
PX Deq: Slave Session Stats
PX Deq: Table Q Close
PX Deq: Table Q Get Keys
PX Deq: Table Q Normal
PX Deq: Table Q Sample
PX Deq: Table Q qref
PX Deq: Test for credit
PX Deq: Test for msg
PX Deq: Txn Recovery Reply
PX Deq: Txn Recovery Start
PX Deq: kdcph_mai
PX Deq: kdcphc_ack
PX Deq: reap credit
PX Deque wait
PX Idle Wait
PX Nsq: PQ descriptor query
PX Nsq: PQ load info query
PX Queuing: statement queue
PX Send Wait
PX create server
PX hash elem being inserted
PX qref latch
PX server shutdown
PX signal server
PX slave connection
PX slave release
Parameter File I/O
RAC referential constraint parent lock
RFS announce
RFS attach
RFS close
RFS create
RFS detach
RFS dispatch
RFS ping
RFS random i/o
RFS register
RFS sequential i/o
RFS write
RMAN Disk slave I/O
RMAN Tape slave I/O
RMAN backup & recovery I/O
RSGA: RAC reconfiguration
RVWR wait for flashback copy
Replication Dequeue
Resolution of in-doubt txns
SGA: MMAN sleep for component shrink
SGA: allocation forcing component growth
SGA: sga_target resize
SQL*Net break/reset to client
SQL*Net break/reset to dblink
SQL*Net message from client
SQL*Net message from dblink
SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data from client
SQL*Net more data from dblink
SQL*Net more data to client
SQL*Net more data to dblink
SQL*Net vector data from client
SQL*Net vector data from dblink
SQL*Net vector data to client
SQL*Net vector data to dblink
SQL*Net vector message from client
SQL*Net vector message from dblink
SUPLOG PL wait for inflight pragma-d PL/SQL
SecureFile mutex
Shared IO Pool IO Completion
Shared IO Pool Memory
Space Manager: slave idle wait
Space Manager: slave messages
Standby redo I/O
Streams AQ: QueueTable kgl locks
Streams AQ: RAC qmn coordinator idle wait
Streams AQ: deallocate messages from Streams Pool
Streams AQ: delete acknowledged messages
Streams AQ: emn coordinator idle wait
Streams AQ: emn coordinator waiting for slave to start
Streams AQ: enqueue blocked due to flow control
Streams AQ: enqueue blocked on low memory
Streams AQ: qmn coordinator idle wait
Streams AQ: qmn coordinator waiting for slave to start
Streams AQ: qmn slave idle wait
Streams AQ: waiting for busy instance for instance_name
Streams AQ: waiting for messages in the queue
Streams AQ: waiting for time management or cleanup tasks
Streams apply: waiting for dependency
Streams apply: waiting to commit
Streams capture: filter callback waiting for ruleset
Streams capture: waiting for archive log
Streams capture: waiting for database startup
Streams capture: waiting for subscribers to catch up
Streams fetch slave: waiting for txns
Streams miscellaneous event
Streams: RAC waiting for inter instance ack
Streams: apply reader waiting for DDL to apply
Streams: flow control
Streams: resolve low memory condition
Streams: waiting for messages
Sync ASM rebalance
TCP Socket (KGAS)
TEST: action hang
TEST: action sync
TEXT: File System I/O
TEXT: URL_DATASTORE network wait
TSE SSO wallet reopen
TSE master key rekey
VKRM Idle
VKTM Init Wait for GSGA
VKTM Logical Idle Wait
WCR: RAC message context busy
WCR: Sync context busy
WCR: capture file IO write
WCR: replay client notify
WCR: replay clock
WCR: replay lock order
WCR: replay paused
Wait for TT enqueue
Wait for Table Lock
Wait for shrink lock
Wait for shrink lock2
Wait on stby instance close
XDB SGA initialization
ack for a broadcasted res from a remote instance
affinity expansion in replay
alter rbs offline
alter system set dispatcher
asynch descriptor resize
auto-sqltune: wait graph update
block change tracking buffer space
blocking txn id for DDL
broadcast mesg queue transition
broadcast mesg recovery queue transition
buffer busy
buffer busy waits
buffer deadlock
buffer dirty disabled
buffer exterminate
buffer freelistbusy
buffer invalidation wait
buffer latch
buffer read retry
buffer rememberlist busy
buffer resize
buffer write wait
buffer writeList full
cell list of blocks physical read
cell manager cancel work request
cell manager closing cell
cell manager discovering disks
cell manager opening cell
cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart flash unkeep
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather
cell worker idle
cell worker online completion
cell worker retry
change tracking file parallel write
change tracking file synchronous read
change tracking file synchronous write
check CPU wait times
checkpoint advanced
checkpoint completed
class slave wait
cleanup of aborted process
cmon timer
concurrent I/O completion
connection pool wait
control file backup creation
control file diagnostic dump
control file heartbeat
control file parallel write
control file sequential read
control file single write
cr request retry
cursor: mutex S
cursor: mutex X
cursor: pin S
cursor: pin S wait on X
cursor: pin X
datafile copy range completion
datafile move cleanup during resize
db file async I/O submit
db file parallel read
db file parallel write
db file scattered read
db file sequential read
db file single write
db flash cache dynamic disabling wait
db flash cache invalidate wait
db flash cache multiblock physical read
db flash cache single block physical read
db flash cache write
dbms_file_transfer I/O
dbverify reads
debugger command
dedicated server timer
direct path read
direct path read temp
direct path sync
direct path write
direct path write temp
dispatcher listen timer
dispatcher shutdown
dispatcher timer
dma prepare busy
dupl. cluster key
enq: AB - ABMR process initialized
enq: AB - ABMR process start/stop
enq: AD - allocate AU
enq: AD - deallocate AU
enq: AD - relocate AU
enq: AE - lock
enq: AF - task serialization
enq: AG - contention
enq: AK - contention
enq: AM - ASM ACD Relocation
enq: AM - ASM Amdu Dump
enq: AM - ASM DD update SrRloc
enq: AM - ASM File Destroy
enq: AM - ASM Grow ACD
enq: AM - ASM Password File Update
enq: AM - ASM User
enq: AM - ASM cache freeze
enq: AM - ASM disk based alloc/dealloc
enq: AM - ASM file descriptor
enq: AM - ASM file relocation
enq: AM - ASM reserved
enq: AM - background COD reservation
enq: AM - block repair
enq: AM - client registration
enq: AM - disk offline
enq: AM - group block
enq: AM - group use
enq: AM - rollback COD reservation
enq: AM - shutdown
enq: AO - contention
enq: AP - contention
enq: AR - contention
enq: AS - service activation
enq: AT - contention
enq: AV - AVD client registration
enq: AV - add/enable first volume in DG
enq: AV - persistent DG number
enq: AV - volume relocate
enq: AW - AW generation lock
enq: AW - AW state lock
enq: AW - AW$ table lock
enq: AW - user access for AW
enq: AY - contention
enq: BB - 2PC across RAC instances
enq: BF - PMON Join Filter cleanup
enq: BF - allocation contention
enq: BM - clonedb bitmap file write
enq: BO - contention
enq: BR - file shrink
enq: BR - multi-section restore header
enq: BR - multi-section restore section
enq: BR - perform autobackup
enq: BR - proxy-copy
enq: BR - request autobackup
enq: BR - space info datafile hdr update
enq: BS - krb spare0
enq: BS - krb spare1
enq: BS - krb spare2
enq: BS - krb spare3
enq: BS - krb spare4
enq: BS - krb spare5
enq: BS - krb spare6
enq: BS - krb spare7
enq: BS - krb spare8
enq: BS - krb spare9
enq: CA - contention
enq: CF - contention
enq: CI - contention
enq: CL - compare labels
enq: CL - drop label
enq: CM - diskgroup dismount
enq: CM - gate
enq: CM - instance
enq: CN - race with init
enq: CN - race with reg
enq: CN - race with txn
enq: CO - master slave det
enq: CQ - contention
enq: CR - block range reuse ckpt
enq: CT - CTWR process start/stop
enq: CT - change stream ownership
enq: CT - global space management
enq: CT - local space management
enq: CT - reading
enq: CT - state
enq: CT - state change gate 1
enq: CT - state change gate 2
enq: CU - contention
enq: CX - TEXT: Index Specific Lock
enq: DB - contention
enq: DD - contention
enq: DF - contention
enq: DG - contention
enq: DI - contention
enq: DL - contention
enq: DM - contention
enq: DN - contention
enq: DO - Staleness Registry create
enq: DO - disk online
enq: DO - disk online operation
enq: DO - disk online recovery
enq: DO - startup of MARK process
enq: DP - contention
enq: DQ - contention
enq: DR - contention
enq: DS - contention
enq: DT - contention
enq: DV - contention
enq: DW - contention
enq: DX - contention
enq: EA - contention
enq: EB - contention
enq: EC - contention
enq: ED - contention
enq: EE - contention
enq: EF - contention
enq: EG - contention
enq: EH - contention
enq: EI - contention
enq: EJ - contention
enq: EK - contention
enq: EL - contention
enq: EM - contention
enq: EN - contention
enq: EO - contention
enq: EP - contention
enq: EQ - contention
enq: ER - contention
enq: ES - contention
enq: ET - contention
enq: EU - contention
enq: EV - contention
enq: EX - contention
enq: EY - contention
enq: EZ - contention
enq: FA - access file
enq: FB - contention
enq: FC - open an ACD thread
enq: FC - recover an ACD thread
enq: FD - Flashback coordinator
enq: FD - Flashback logical operations
enq: FD - Flashback on/off
enq: FD - Marker generation
enq: FD - Restore point create/drop
enq: FD - Tablespace flashback on/off
enq: FE - contention
enq: FG - FG redo generation enq race
enq: FG - LGWR redo generation enq race
enq: FG - serialize ACD relocate
enq: FL - Flashback database log
enq: FL - Flashback db command
enq: FM - contention
enq: FP - global fob contention
enq: FR - contention
enq: FR - recover the thread
enq: FR - use the thread
enq: FS - contention
enq: FT - allow LGWR writes
enq: FT - disable LGWR writes
enq: FU - contention
enq: FW - contention
enq: FX - issue ACD Xtnt Relocation CIC
enq: FZ - contention
enq: GA - contention
enq: GB - contention
enq: GC - contention
enq: GD - contention
enq: GE - contention
enq: GF - contention
enq: GG - contention
enq: GH - contention
enq: GI - contention
enq: GJ - contention
enq: GK - contention
enq: GL - contention
enq: GM - contention
enq: GN - contention
enq: GO - contention
enq: GP - contention
enq: GQ - contention
enq: GR - contention
enq: GS - contention
enq: GT - contention
enq: GU - contention
enq: GV - contention
enq: GX - contention
enq: GY - contention
enq: GZ - contention
enq: HD - contention
enq: HP - contention
enq: HQ - contention
enq: HV - contention
enq: HW - contention
enq: IA - contention
enq: ID - contention
enq: IL - contention
enq: IM - contention for blr
enq: IR - contention
enq: IR - contention2
enq: IS - contention
enq: IT - contention
enq: IV - contention
enq: IZ - contention
enq: JD - contention
enq: JI - contention
enq: JQ - contention
enq: JS - aq sync
enq: JS - contention
enq: JS - evt notify
enq: JS - evtsub add
enq: JS - evtsub drop
enq: JS - job recov lock
enq: JS - job run lock - synchronize
enq: JS - q mem clnup lck
enq: JS - queue lock
enq: JS - sch locl enqs
enq: JS - wdw op
enq: JX - SQL statement queue
enq: JX - cleanup of queue
enq: KD - determine DBRM master
enq: KE - contention
enq: KL - contention
enq: KM - contention
enq: KO - fast object checkpoint
enq: KP - contention
enq: KQ - access ASM attribute
enq: KT - contention
enq: LA - contention
enq: LB - contention
enq: LC - contention
enq: LD - contention
enq: LE - contention
enq: LF - contention
enq: LG - contention
enq: LH - contention
enq: LI - contention
enq: LJ - contention
enq: LK - contention
enq: LL - contention
enq: LM - contention
enq: LN - contention
enq: LO - contention
enq: LP - contention
enq: LQ - contention
enq: LR - contention
enq: LS - contention
enq: LT - contention
enq: LU - contention
enq: LV - contention
enq: LX - contention
enq: LY - contention
enq: LZ - contention
enq: MD - contention
enq: MH - contention
enq: MK - contention
enq: ML - contention
enq: MN - contention
enq: MO - contention
enq: MR - contention
enq: MR - datafile online
enq: MR - standby role transition
enq: MS - contention
enq: MV - datafile move
enq: MW - contention
enq: MX - sync storage server info
enq: NA - contention
enq: NB - contention
enq: NC - contention
enq: ND - contention
enq: NE - contention
enq: NF - contention
enq: NG - contention
enq: NH - contention
enq: NI - contention
enq: NJ - contention
enq: NK - contention
enq: NL - contention
enq: NM - contention
enq: NN - contention
enq: NO - contention
enq: NP - contention
enq: NQ - contention
enq: NR - contention
enq: NS - contention
enq: NT - contention
enq: NU - contention
enq: NV - contention
enq: NX - contention
enq: NY - contention
enq: NZ - contention
enq: OC - contention
enq: OD - Serializing DDLs
enq: OL - contention
enq: OQ - xsoq*histrecb
enq: OQ - xsoqhiAlloc
enq: OQ - xsoqhiClose
enq: OQ - xsoqhiFlush
enq: OQ - xsoqhistrecb
enq: OT - TEXT: Generic Lock
enq: OW - initialization
enq: OW - termination
enq: PD - contention
enq: PE - contention
enq: PF - contention
enq: PG - contention
enq: PH - contention
enq: PI - contention
enq: PL - contention
enq: PM - contention
enq: PR - contention
enq: PS - contention
enq: PT - contention
enq: PV - syncshut
enq: PV - syncstart
enq: PW - flush prewarm buffers
enq: PW - perwarm status in dbw0
enq: QA - contention
enq: QB - contention
enq: QC - contention
enq: QD - contention
enq: QE - contention
enq: QF - contention
enq: QG - contention
enq: QH - contention
enq: QI - contention
enq: QJ - contention
enq: QK - contention
enq: QL - contention
enq: QM - contention
enq: QN - contention
enq: QO - contention
enq: QP - contention
enq: QQ - contention
enq: QR - contention
enq: QS - contention
enq: QT - contention
enq: QU - contention
enq: QV - contention
enq: QX - contention
enq: QY - contention
enq: QZ - contention
enq: RB - contention
enq: RC - Result Cache: Contention
enq: RD - RAC load
enq: RE - block repair contention
enq: RF - DG Broker Current File ID
enq: RF - FSFO Observer Heartbeat
enq: RF - FSFO Primary Shutdown suspended
enq: RF - RF - Database Automatic Disable
enq: RF - atomicity
enq: RF - new AI
enq: RF - synch: DG Broker metadata
enq: RF - synchronization: aifo master
enq: RF - synchronization: critical ai
enq: RK - set key
enq: RL - RAC wallet lock
enq: RM - contention
enq: RN - contention
enq: RO - contention
enq: RO - fast object reuse
enq: RP - contention
enq: RR - contention
enq: RS - file delete
enq: RS - persist alert level
enq: RS - prevent aging list update
enq: RS - prevent file delete
enq: RS - read alert level
enq: RS - record reuse
enq: RS - write alert level
enq: RT - contention
enq: RT - thread internal enable/disable
enq: RU - contention
enq: RU - waiting
enq: RW - MV metadata contention
enq: RX - relocate extent
enq: RX - unlock extent
enq: SB - logical standby metadata
enq: SB - table instantiation
enq: SC - contention
enq: SE - contention
enq: SF - contention
enq: SH - contention
enq: SI - contention
enq: SJ - Slave Task Cancel
enq: SK - contention
enq: SL - escalate lock
enq: SL - get lock
enq: SL - get lock for undo
enq: SM - contention
enq: SO - contention
enq: SP - contention 1
enq: SP - contention 2
enq: SP - contention 3
enq: SP - contention 4
enq: SQ - contention
enq: SR - contention
enq: SS - contention
enq: ST - contention
enq: SU - contention
enq: SV - contention
enq: SW - contention
enq: SX - contention 5
enq: SX - contention 6
enq: TA - contention
enq: TB - SQL Tuning Base Cache Load
enq: TB - SQL Tuning Base Cache Update
enq: TC - contention
enq: TC - contention2
enq: TD - KTF dump entries
enq: TE - KTF broadcast
enq: TF - contention
enq: TH - metric threshold evaluation
enq: TK - Auto Task Serialization
enq: TK - Auto Task Slave Lockout
enq: TL - contention
enq: TM - contention
enq: TO - contention
enq: TP - contention
enq: TQ - DDL contention
enq: TQ - DDL-INI contention
enq: TQ - INI contention
enq: TQ - TM contention
enq: TS - contention
enq: TT - contention
enq: TW - contention
enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention
enq: UL - contention
enq: US - contention
enq: VA - contention
enq: VB - contention
enq: VC - contention
enq: VD - contention
enq: VE - contention
enq: VF - contention
enq: VG - contention
enq: VH - contention
enq: VI - contention
enq: VJ - contention
enq: VK - contention
enq: VL - contention
enq: VM - contention
enq: VN - contention
enq: VO - contention
enq: VP - contention
enq: VQ - contention
enq: VR - contention
enq: VS - contention
enq: VT - contention
enq: VU - contention
enq: VV - contention
enq: VX - contention
enq: VY - contention
enq: VZ - contention
enq: WA - contention
enq: WF - contention
enq: WG - delete fso
enq: WG - lock fso
enq: WL - RAC-wide SGA contention
enq: WL - RFS global state contention
enq: WL - Test access/locking
enq: WL - contention
enq: WM - WLM Plan activation
enq: WP - contention
enq: WR - contention
enq: WS - contention
enq: XB - contention
enq: XC - XDB Configuration
enq: XD - ASM disk OFFLINE
enq: XD - ASM disk ONLINE
enq: XD - ASM disk drop/add
enq: XH - contention
enq: XL - fault extent map
enq: XQ - purification
enq: XQ - recovery
enq: XQ - relocation
enq: XR - database force logging
enq: XR - quiesce database
enq: XY - contention
enq: YA - contention
enq: YB - contention
enq: YC - contention
enq: YD - contention
enq: YE - contention
enq: YF - contention
enq: YG - contention
enq: YH - contention
enq: YI - contention
enq: YJ - contention
enq: YK - contention
enq: YL - contention
enq: YM - contention
enq: YN - contention
enq: YO - contention
enq: YP - contention
enq: YQ - contention
enq: YR - contention
enq: YS - contention
enq: YT - contention
enq: YU - contention
enq: YV - contention
enq: YX - contention
enq: YY - contention
enq: YZ - contention
enq: ZA - add std audit table partition
enq: ZF - add fga audit table partition
enq: ZG - contention
enq: ZH - compression analysis
enq: ZX - repopulation file write
enq: ZZ - update hash tables
events in waitclass Other
extent map load/unlock
external table misc IO
external table open
external table read
external table seek
external table write
fbar timer
first spare wait event
flashback buf free by RVWR
flashback free VI log
flashback log file read
flashback log file sync
flashback log file write
flashback log switch
force-cr-override flush
free buffer waits
free global transaction table entry
free process state object
gc assume
gc block recovery request
gc buffer busy acquire
gc buffer busy release
gc cancel retry
gc claim
gc cr block 2-way
gc cr block 3-way
gc cr block busy
gc cr block congested
gc cr block lost
gc cr block unknown
gc cr cancel
gc cr disk read
gc cr disk request
gc cr failure
gc cr grant 2-way
gc cr grant busy
gc cr grant congested
gc cr grant unknown
gc cr multi block request
gc cr request
gc current block 2-way
gc current block 3-way
gc current block busy
gc current block congested
gc current block lost
gc current block unknown
gc current cancel
gc current grant 2-way
gc current grant busy
gc current grant congested
gc current grant unknown
gc current multi block request
gc current request
gc current retry
gc current split
gc domain validation
gc flushed buffer
gc freelist
gc object scan
gc quiesce
gc recovery
gc recovery free
gc recovery quiesce
gc remaster
gcs ddet enter server mode
gcs domain validation
gcs drm freeze begin
gcs drm freeze in enter server mode
gcs enter server mode
gcs lmon dirtydetach step completion
gcs log flush sync
gcs remastering wait for read latch
gcs remote message
gcs resource directory to be unfrozen
gcs retry nowait latch get
gcs to be enabled
ges DFS hang analysis phase 2 acks
ges LMD suspend for testing event
ges LMD to inherit communication channels
ges LMD to shutdown
ges LMON for send queues
ges LMON to get to FTDONE
ges LMON to join CGS group
ges RMS0 retry add redo log
ges cached resource cleanup
ges cancel
ges cgs registration
ges client process to exit
ges enter server mode
ges generic event
ges global resource directory to be frozen
ges inquiry response
ges lmd and pmon to attach
ges lmd sync during reconfig
ges lmd/lmses to freeze in rcfg
ges lmd/lmses to unfreeze in rcfg
ges lms sync during dynamic remastering and reconfig
ges master to get established for SCN op
ges message buffer allocation
ges performance test completion
ges pmon to exit
ges process with outstanding i/o
ges remote message
ges resource cleanout during enqueue open
ges resource cleanout during enqueue open-cvt
ges resource directory to be unfrozen
ges retry query node
ges reusing os pid
ges user error
ges wait for lmon to be ready
ges yield cpu in reconfig
ges/gcs diag dump
ges1 LMON to wake up LMD - mrcvr
ges2 LMON to wake up LMD - mrcvr
ges2 LMON to wake up lms - mrcvr 2
ges2 LMON to wake up lms - mrcvr 3
ges2 proc latch in rm latch get 1
ges2 proc latch in rm latch get 2
global cache busy
global enqueue expand wait
global plug and play automatic resource creation
heartbeat monitor sleep
i/o slave wait
imm op
inactive session
inactive transaction branch
index (re)build online cleanup
index (re)build online merge
index (re)build online start
index block split
instance state change
io done
job scheduler coordinator slave wait
jobq slave TJ process wait
jobq slave shutdown wait
jobq slave wait
kcbzps
kdblil wait before retrying ORA-54
kdic_do_merge
kfcl: instance recovery
kfk: async disk IO
kgltwait
kjbdomalc allocate recovery domain - retry
kjbdrmcvtq lmon drm quiesce: ping completion
kjbopen wait for recovery domain attach
kjctcisnd: Queue/Send client message
kjctssqmg: quick message send wait
kjudomatt wait for recovery domain attach
kjudomdet wait for recovery domain detach
kjxgrtest
kkdlgon
kkdlhpon
kkdlsipon
kksfbc child completion
kksfbc research
kkshgnc reloop
kksscl hash split
knpc_acwm_AwaitChangedWaterMark
knpc_anq_AwaitNonemptyQueue
knpsmai
kpodplck wait before retrying ORA-54
ksbcic
ksbsrv
ksdxexeother
ksdxexeotherwait
ksim generic wait event
kslwait unit test event 1
kslwait unit test event 2
kslwait unit test event 3
ksv slave avail wait
ksxr poll remote instances
ksxr wait for mount shared
ktfbtgex
ktm: instance recovery
ktsambl
kttm2d
kupp process wait
kxfxse
kxfxsp
latch activity
latch free
latch: Change Notification Hash table latch
latch: In memory undo latch
latch: MQL Tracking Latch
latch: PX hash array latch
latch: Undo Hint Latch
latch: WCR: processes HT
latch: WCR: sync
latch: active service list
latch: cache buffer handles
latch: cache buffers chains
latch: cache buffers lru chain
latch: call allocation
latch: change notification client cache latch
latch: checkpoint queue latch
latch: enqueue hash chains
latch: gc element
latch: gcs resource hash
latch: ges resource hash list
latch: lob segment dispenser latch
latch: lob segment hash table latch
latch: lob segment query latch
latch: messages
latch: object queue header operation
latch: parallel query alloc buffer
latch: redo allocation
latch: redo copy
latch: redo writing
latch: row cache objects
latch: session allocation
latch: shared pool
latch: undo global data
latch: virtual circuit queues
libcache interrupt action by LCK
library cache load lock
library cache lock
library cache pin
library cache revalidation
library cache shutdown
library cache: mutex S
library cache: mutex X
listen endpoint status
listener registration dump
lms flush message acks
local write wait
lock close
lock deadlock retry
lock escalate retry
lock release pending
lock remastering
log buffer space
log file parallel write
log file sequential read
log file single write
log file switch (archiving needed)
log file switch (checkpoint incomplete)
log file switch (clearing log file)
log file switch (private strand flush incomplete)
log file switch completion
log file sync
log switch/archive
log write(even)
log write(odd)
logout restrictor
master diskmon read
master diskmon startup
master exit
multiple dbwriter suspend/resume for file offline
name-service call wait
no free buffers
no free locks
null event
opishd
optimizer stats update retry
os thread startup
parallel recovery change buffer free
parallel recovery control message reply
parallel recovery coord send blocked
parallel recovery coord wait for reply
parallel recovery coordinator waits for slave cleanup
parallel recovery read buffer free
parallel recovery slave idle wait
parallel recovery slave next change
parallel recovery slave wait for change
pending global transaction(s)
pi renounce write complete
pipe get
pipe put
pmon dblkr tst event
pmon timer
pool server timer
prewarm transfer retry
prior spawner clean up
process diagnostic dump
process shutdown
process startup
process terminate
qerex_gdml
queue slave messages
rdbms ipc message
rdbms ipc message block
rdbms ipc reply
read by other session
readable standby redo apply remastering
recovery area: computing applied logs
recovery area: computing backed up files
recovery area: computing dropped files
recovery area: computing obsolete files
recovery buffer pinned
recovery instance recovery completion
recovery read
reliable message
remote db file read
remote db file write
remote db operation
resmgr:become active
resmgr:cpu quantum
resmgr:internal state change
resmgr:internal state cleanup
resmgr:large I/O queued
resmgr:pq queued
resmgr:sessions to exit
resmgr:small I/O queued
retry contact SCN lock master
rollback operations active
rollback operations block full
rolling migration: cluster quiesce
row cache cleanup
row cache lock
row cache process
row cache read
scginq AST call
second spare wait event
secondary event
securefile chain update
securefile direct-read completion
securefile direct-write completion
select wait
set director factor wait
shared server idle wait
shutdown after switchover to standby
simulated log write delay
single-task message
slave exit
smon timer
sort segment request
standby query scn advance
statement suspended, wait error to be cleared
switch logfile command
switch undo - offline
test long ops
timer in sksawat
transaction
tsm with timeout
txn to complete
unbound tx
undo segment extension
undo segment recovery
undo segment tx slot
undo_retention publish retry
unspecified wait event
utl_file I/O
virtual circuit next request
virtual circuit wait
wait active processes
wait for EMON to process ntfns
wait for EMON to spawn
wait for FMON to come up
wait for MTTR advisory state object
wait for a paralle reco to abort
wait for a undo record
wait for another txn - rollback to savepoint
wait for another txn - txn abort
wait for another txn - undo rcv abort
wait for assert messages to be sent
wait for change
wait for kkpo ref-partitioning *TEST EVENT*
wait for master scn
wait for possible quiesce finish
wait for scn ack
wait for stopper event to be increased
wait for sync ack
wait for unread message on broadcast channel
wait for unread message on multiple broadcast channels
wait for verification ack
wait list latch activity
wait list latch free
waiting to get CAS latch
waiting to get RM CAS latch
watchdog main loop
write complete waits
write complete waits: flash cache
writes stopped by instance recovery or database suspension
xdb schema cache initialization

Tuesday, October 21, 2014

Scope Oracle Database 10g

http://docs.oracle.com/cd/B19306_01/server.102/b14231.pdf

1) Roles/Privileges

2) ORAPWD

3) Remote_login_passwordfile

4) Considerations before creating the database

5) Specifying Database time zone and time zone file

6) Specifying force logging mode

7) Database Block Size

8) Managing SGA

9) Specifing the method of Undo Space Management

10) Specifying the max number of process

11) Setting COMPATIBLE

12) Setting the license parameter

13) Exporting Server Parameter file

14) Backing up the server parameter file

15) Error and Recovery for the server parameter file

16) Viewing parameter settings

17) Defining Application Services for Oracle Database 10g

18) Enabling Transparent Data Encryption

19) Creating a secure external password store

20) Restricting access to an open database

21) Quiescing a database

22) Suspending and Resuming a database

23) Dedicated server processes

24) Shared server processes

25) Intialization parameter for shared server

26) Oracle database background processes

27) Managing Processes for Parallel SQL Execution

28) Managing Processes for External Procedures

29) Terminating Sessions

30) Monitoring the operation of your database

31) Backup control files

32) Manage the size of control files

33) Handling errors during create controlfile

34) Backing up control files

35) Redo log contents

36) How Oracle database writes to the Redo Log

37) Verifying Blocks in Redo log files

38) Clearing a redo log files

39) Viewing Redo log information

40) Performing Manual Archiving

41) Adjusting the number of archiver processes

42) Understanding archive destination status

43) Normal transmission mode

44) Standby transmission mode

45) Controlling trace outout generated by the archivelog process

46) Viewing information about the archive redo log

47) Bigfile tablespaces

48) Multiple Temporary Tablespaces : Using Tablespace Groups

49) Specifying non standard block sizes for tablespaces

50) Controlling the writing of redo records

51) Creating a read-only tablespace on a WORM Device

52) Delaying the opening of datafiles in Read-Only Tablespace

53) Monitoring the Occupants of SYSAUX Tablespace

54) Moving Occupants Out of or Into SYSAUX Tablespace

55) Controlling the size of the SYSAUX Tablespace

56) Diagnosing and repairing Locally Mananged Tablespace Problems

57) Migrating the SYSTEM Tablespace to a locally Managed Tablespace

58) Transporting tablespace between database

59) Viewing tablespace information

60) File Transfer and DBMS_SCHEDULER Package

61) Advanced File transfer Mechanisms

62) Mapping file to Physical Devices

63) Sizing the Undo Tablespace

64) Managing Undo Tablespaces

65) Viewing Information about undo

66) What are Oracle Managed files

67) Behavior of Oracle-Managed Files

68) Scenarios for Using Oracle-Managed Files

69) Administering an ASM Instance

70) Administering ASM Disk groups

71) Using ASM In the databse

72) Accessing ASM with the XML DB Virtual Folder

73) Viewing information about ASM

74) Truncate Table and Cluster

75) Enabling and Disabling Triggers

76) Managing Integrity Constraints

77) Managing Object Dependencies

78) Managing Object Name resolution

79) Switching to a different schema

80) Displaying information about schema objects

81) Managing Space in Data Blocks

82) Managing Storage Parameters

83) Managing Resumable Space allocation

84) Reclaiming Wasted Space

85) Understanding Space Usage of Datatypes

86) Displaying Information about space usage of schema objects

87) Capacity Planning for Database Objects

88) Guidelines for Managing Tables

89) Parallelizing table creation

90) Loading Tables

91) Automatically collecting statistics on Tables

92) Redefining Tables Online

93) Auditing Table changes using flashback transaction query

94) Recovering tables using the flashback table features

95) Using flashback drop and Managing the recycle bin

96) Managing IOT

97) Managing External Tables

98) Viewing information about tables

99) Guidelines for Managing Indexes

100) Monitoring Space Use of Indexes

101) Viewing Index Information

102) Partitioning Methods

103) Marinating Partitioned Tables

104) Guidelines for Managing Clusters

105) Managing hash clusters

106) Managing views,sequences and synonyms

107) Using DBMS_REPAIR to repair data block corruption

108) Managing Automatic System Tasks Using the Maintenance Window

109) Using Database Resource Manager

110) Moving from DBMS_JOB to DBMS_SACHEDULER

111) SCHEDULER CONCEPTS

112) Distributed database concepts

113) Database Link

114) Transaction Processing in a distributed system

115) Managing a distributed databases

116) Distributed transaction concepts

117) Managing Distributed Transactions

Monday, October 20, 2014

Using External Table

Step 1) Check the listener log location

Lsnrctl status

Listener Log File /u01/app/oracle/diag/tnslsnr/PRDG11G/listener/alert/log.xml

Step 2) Create directory

Sql > create directory listener_log_directory as ‘/u01/app/oracle/diag/tnslsnr/PRDG11G/listener/alert’ ;

Step 3) create a external table

create table listener_log_detail

(

log_date date,

connect_string varchar2(300),

protocol_info varchar2(300),

action varchar2(15),

service_name varchar2(15),

return_code number(10)

)

organization external (

type oracle_loader

default directory listener_log_directory

access parameters

(

records delimited by newline

nobadfile

nologfile

nodiscardfile

fields terminated by "*" lrtrim

missing field values are null

(

log_date char(30) date_format

date mask "DD-MON-YYYY HH24:MI:SS",

connect_string,

protocol_info,

action,

service_name,

return_code

)

location ('log.xml')

)

reject limit unlimited

Step 4) Create function for parsing the listener log

create or replace function parse_listener_log_file

(

p_in varchar2,

p_param in varchar2

)

return varchar2

l_begin number(3);

l_end number(3);

l_val varchar2(2000);

begin

if p_param not in (

'SID',

'SERVICE_NAME',

'PROGRAM',

'SERVICE',

'HOST',

'USER',

'PROTOCOL',

'TYPE',

'METHOD',

'RETRIES',

'DELAY',

'PORT',

'COMMAND'

) then

raise_application_error (-20001,'Invalid Parameter Value

'||p_param);

end if;

l_begin := instr (upper(p_in), '('||p_param||'=');

l_begin := instr (upper(p_in), '=', l_begin);

l_end := instr (upper(p_in), ')', l_begin);

l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);

return l_val;

end;

Step 5) Execute the following query

Sql > set null ?

Sql > select parse_listener_log_file(connect_string,’USER’) from listener_log_detail;

Find values when listener was stopped

col host format a20

col l_user format a20

col service format a15

col logdate format a20

select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate,

parse_listener_log_file(connect_string,'HOST') host,

parse_listener_log_file(connect_string,'USER') l_user,

parse_listener_log_file(connect_string,'SERVICE') service

from listener_log_detail

where parse_listener_log_file(connect_string, 'COMMAND') = 'stop';

Program Usage

col program format a70

col cmt format 999,999

select parse_listener_log_file(connect_string,'PROGRAM') program,

count(1) cnt

from listener_log_detail

group by parse_listener_log_file(connect_string,'PROGRAM');

Untitled 1

Undo Table Analysis

For Single Instance Database

1) Check parameter undo_management

show parameter undo_management // If auto it means undo auto management is enabled

2) Name of undo tablespaces

select tablespace_name,contents from dba_tablespaces where contents='UNDO';

3) Details of segment generated by users/schema

Select owner,segment_name,tablespace_name from dba_rollback_Segs;

4) Detail of segment name and its status (Active/Expired/Unexpired)

Select owner,segment_name,tablespace_name from dba_rollback_Segs;

5) Detail of MB/Percent of Active,Expired & Unexpired segment

select status,round(sum_bytes / (1024*1024), 0) as MB,round((sum_bytes / undo_size) * 100, 0) as PERC from (select status, sum(bytes) sum_bytes from dba_undo_extents group by status),(select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE';

6) Check the value of maxquerylen to suggest the value of undo_retnetion

select max(maxquerylen) from v$undostat;

7) Check the undo_retention type

select tablespace_name, retention from dba_tablespaces;

For RAC as each instance has separate undo tablespace . The table name in the above query needs to be appeneded with G

Saturday, October 11, 2014

Untitled 1

Enabling flashback,restore point and restore point in database

Commands as below

Sql > select flashback_on from v$database

If no then follow below step

sql > shutdown immediate

sql> startup mount

sql > alter database flashback on

sql> shutdown immediate

sql> startup

sql> select flashback_on from v$database

sql> create restore point before_upgrade

sql > create restore point befor_upg guarantee flashback database

sql> select * from v$restore_point

sql > exit

rman target /

rman > list restore point all

sql > shutdown immediate

sql > startup mount

sql> flashback database to restore point before_upgrade

sql> shutdown immediate

sql > startup mount

sql>alter database open resetlogs;

sql > select * from v$restore_point

sql> drop restore point before_upgrade

sql>drop restore point befor_upg

Saturday, October 4, 2014

How to Install rpm package on linux

rpm -ivh <package_name.rpm> // extension rpm is required at the last of package name

How to uninstall rpm packge on linux

rpm -ev <package_name> // no rpm extension is required at the last of package name

Update statement taking time to complete

Scenario :-- I executed update statement and it ran giving 1 row updated . Then putty session timed out

I reconnected to putty session and executed the select query to check whether rows has been updated or not .

It showed me rows not updated

I was worried as if happened

I reran the update query to update the rows then it hang for long time

I did following analysis

1) Checked the filesystem usage :-- Its was ok
2) Check the tablespace usage :-- It was ok
3) Check the oracle alert log it was ok
4) Then i checked the locks in the database and i found that there is lock in the table which needs to be updated
5) I killed the Inactive session to release the lock

The update query which was hung completed by saying 0 rows updated

I ran again the select query and this time i can see the values are updated

Friday, October 3, 2014

Select statement modifies blocks in two situations: first when the option for update is specified,and second when deferred block cleanout occurs

to store fewer rows per block ,either a higher pctfree or a smaller block size can be used

the goal of freelist is to spread concurrent insert statements over several blocks

To have data compression at the table level . Following condition must be full fulled

1) The table cannot have more than 255 columns
2) The table cannot be created with parameter rowdependencies

Free space in the compressed block caused by delete statements is never used

Data compression should be used for read-only table

Data Marts and Completely refreshed materialzed views are good candidate for data compression

Upgrading Oracle database 11.2.0.2 to 11.2.0.4

1) Patch Set to 11.2.0.3 has been suspended by oracle it means we need to go to 11.2.0.4

Step 1) Download and Install the 11.2.0.4 software binaries
Step 2) Run the pre-upgarde tool . From the existing oracle instance
$ORACLE_HOME/rdbms/admin/utlu112i.sql

If pre-upgrade tool is not run then error is obtained at the time of upgrade

Step 3) Run dbupgdiag.sql script from the below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects exist in dba_objects

Metalink id :-- Note 556610.1 ( For downloading the script for step 3)

Step 4)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

Step 5) After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

Step 6) Take the backup of the database before upgrade

rman target /
run
{
backup database plus archivelog ;
}

Step 7) Shutdown the database

Step 8) Copy the pfile,passwordfile to new dbs directory from old dbs directory

Step 9) Copy the listener.ora,tnsnames.ora and sqlnet.ora from old $ORACLE_HOME/network/admin to new $ORACLE_HOME/network/admin

Step 10) Set ORACLE_HOME to new binary location

Step 11) sqlplus / as sysdba
sql > startup upgrade
sql > @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
sql > shutdown immediate
Step 12) Restart the database in normal mode

Step 13) @$ORACLE_HOME/rdbms/admin/catuppst.sql

Step 14) @$ORACLE_HOME/rdbms/admin/utlrp.sql;

Step 15) Run Script from step 3

Step 16) Upgrade the time zone to latest version using DBMS_DST

Follow document link Note 1201253.1

Tuesday, September 30, 2014

Parallel Processing

Parallel Processing should be used under this two conditions

1) When plenty of free resource is available . The aim of PX is to reduce the respnose time by distributing the work done by single process

2) It can used for SQL statements that take more than a dozen seconds to execute serially

If PX is commonly used for many SQL statements,the degree of parallelism should be set at the table or index level.

If it is used only for specific batches or reports . It is better to enable it at the session level or through hints

Insert statements with values clause cannot be parallelized

DML --> Insert,update,delete & merge can be executed in parallel when

1) Table has a trigger
2) A table has either a foreign key constraint refering itself
3) an object column is modified
4) a clustered or temporary table is modified

An actual distribution for a SQL statement , you can use the dynamic performance view v$pq_tqstat.

Information provided for current session and last SQL statements

Direct path insert gives better performance as it generates minimum undo . In fact undo is generated for only space management operation . For Ex :-- To increase the high watermark and to add new extent to the segment and not for the rows contained in the blocks that are inserted by direct-path .

The purpose of minimal logging is t
\'o minimize the redo generation

You can set minimual logging by setting parameter nologging at the table or partition level

Mimimial loggging is supported for direct path loads and some DDL Statements

Fetching numerous row at a time is called row prefetching

Row prefetching is enabled by JDBC drive by default