Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem: enq: TX -
row lock contention" for "select sysdate from sys.dual" statement
.
DATABASE HANG AND & LARGE NUMBER OF LOCKS &
WAIT ENQ: TX - ROW LOCK CONTENTIN
The database was hanging and getting large number of
locks & wait event enq: TX - row lock
contention" for "select sysdate from sys.dual" statement .
contention" for "select sysdate from sys.dual" statement .
Log files:
SID_ora_25646_HANG.trc
-------------------------
...
Aborting this subtree dump because of bad address d2031e60
SID_ora_2123_HANG.trc
----------------------------------
ABORTING SYSTEM STATE DUMP: ANOTHER PROCESS (so = 0xc00000005f0857a8) EXECUTING SYSTEM STATE DUMP
-------------------------
...
Aborting this subtree dump because of bad address d2031e60
SID_ora_2123_HANG.trc
----------------------------------
ABORTING SYSTEM STATE DUMP: ANOTHER PROCESS (so = 0xc00000005f0857a8) EXECUTING SYSTEM STATE DUMP
Solution:
get some system state dumps :
Login to sqlplus as the internal user:
sqlplus "/ as sysdba"
set trace file size to unlimited:
alter session set max_dump_file_size = unlimited;
alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';
Login to sqlplus as the internal user:
sqlplus "/ as sysdba"
set trace file size to unlimited:
alter session set max_dump_file_size = unlimited;
alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';
You can also try and catch the blocker and get
errorstacks to see what the blocker is doing at the time
To find the the blocker you can use script like
Note.1020008.6 Ext/Pub SCRIPT FULLY DECODED LOCKING:
Note.1020012.6 Ext/Pub SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO:
Then using spid for the blocker get errorstacks as follows:
login to SQL*Plus:
connect / as sysdba
oradebug setospid xxxx
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug event 10046 trace name context off
This will produce a trace file in the user_dump_dest
To find the the blocker you can use script like
Note.1020008.6 Ext/Pub SCRIPT FULLY DECODED LOCKING:
Note.1020012.6 Ext/Pub SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO:
Then using spid for the blocker get errorstacks as follows:
login to SQL*Plus:
connect / as sysdba
oradebug setospid xxxx
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug event 10046 trace name context off
This will produce a trace file in the user_dump_dest
References:
Note 62354.1 TX Transaction locks - Example wait
scenarios
6 comments:
I гead this artiсle сomplеtely соnceгning the cоmparison of newest and precеding tеchnologiеs, it's amazing article.
Review my webpage; bucket truck
Look into my weblog ... altec bucket trucks for sale
Hellο thеre, I discovered your
ωebsitе by way of Google ωhіlst searchіng
fоr а гelatеd topіc, yοuг ωeb site camе up,
іt appeaгѕ great. I've bookmarked it in my google bookmarks.
Hello there, just became aware of your weblog via Google, and located that it is really informative. I am gonna be careful for brussels. I'll appreсiаte for thоsе ωhо pгoсeеd this in futurе.
A lot оf othеr ρeοple wіll pгobably be benefitеd frοm youг wrіting.
Cheеrs!
Feel free tо suгf to my hοmepage: cheap car insurance dallas
Also visit my site :: dallas tx auto insurance
Hoωdy! Thiѕ is my fiгѕt visіt to your blog!
We аre a grouр of ѵolunteers and startіng а new іnitiative
in a сommunity in the samе niche. Youг blog
provided us beneficіal informatiоn to work on.
Үou have done а wonderful јob!
my wеb blog ... http://www.biggreeneggguide.com/
Also see my page :: how to Put together a big green Egg
I'm not sure where you are getting your information, but good topic. I needs to spend some time learning more or understanding more. Thanks for magnificent information I was looking for this info for my mission.
Feel free to visit my web site :: eti bucket truck for sale
If you would like to increasе уоur expеrіence simply keep νisiting this ωеb pаge and be updated with the newest neωs posteԁ herе.
Here is mу website www.lgtensunits.com
My website > Tens 7000 Deluxe Dual Unit
My relatives all the time say that I am wasting my time here at web, but I know I am getting experience all the
time by reading thes fastidious articles.
Feel free to surf to my page diets that work fast for women
My web page :: diet plans that work
Post a Comment