Re: locks through PL-SQL
Thanks a lot for the explaination. I got it.
Regards,
~Dilip
[EMAIL PROTECTED] wrote:
Closing a cursor doesn't release the lock.
Think of it in SQL*PLUS. If you issue a SELECT ... FOR UPDATE statment, it
opens an implicit cursor returns the rows and closes the cursor. But it
doesn't release the lock.
Locks can only be released by COMMIT/ROLLBACK
Regards
Naveen
-Original Message-
Sent: Friday, January 03, 2003 4:44 PM
To: Multiple recipients of list ORACLE-L
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
Content-Type: text/plain; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as follows:
---
CREATE OR REPLACE procedure b as
cursor sel_up is select * from emp_info for update;
begin
For emp_rec in sel_up1 LOOP
dbms_output.put_line('Hi');
End loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it should
acquire the lock on the rows and when procedure ends it should release locks
as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com
Buy the best in Movies at http://www.videos.indiatimes.com
Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines
Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
Content-Type: text/html; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as
follows:
---
CREATE size=2>OR size=2>REPLACE size=2>procedure b
color=#f0 size=2>as
cursorsize=2> sel_up issize=2> selectsize=2> *
from
emp_info forsize=2> update;size=2>
begin
For emp_rec size=2>in sel_up1
color=#f0 size=2>LOOP
dbms_output.put_line('Hi'
color=#f0 size=2>);
End size=2>loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it
should acquire the lock on the rows and when procedure ends it should release
locks as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from
Indiatimes at
face="Arial" size="2">http://email.indiatimes.com
Buy the
best in Movies at
href="http://www.videos.indiatimes.com">http://www.videos.indiatimes.com<;
br>Now bid just 7 Days in Advance and get
Huge Discounts on Indian Airlines Flights. So log on to
href="http://indianairlines.indiatimes.com">http://indianairlines.indiatimes.
com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: dilip7772002
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com
Buy the best in Movies at http://www.videos.indiatimes.com
Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines Flights. So
log on to http://indianairlines.indiatimes.com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_20031461531571734575198
Content-Type: text/html; charset=us-ascii
Thanks a lot for the explaination. I got it.
Regards,
~Dilip
[EMAIL PROTECTED] wrote:
Closing a cursor doesn't release the lock.Think of it in SQL*PLUS.
If you issue a SELECT ... FOR UPDATE statment, itopens an implicit cursor returns
the rows and c
Re: locks through PL-SQL
Consider a simple SQL*Plus analogy:
SQL> select * from emp_info for update;
list of columns appears
N rows selected
SQL>
The rows are still locked.
Why should pl/sql behave differently from SQL*Plus.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
England__January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 03 January 2003 11:56
>--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
>Content-Type: text/plain; charset=us-ascii
>
>Hi List,
>
>
>Little confused about the locks. I wrote a plsql procedure as
follows:
>
>
>---
>
>
>CREATE OR REPLACE procedure b as
>
>
>cursor sel_up is select * from emp_info for update;
>
>
>begin
>
>
>For emp_rec in sel_up1 LOOP
>
>
>dbms_output.put_line('Hi');
>
>
>End loop;
>
>
>end;
>
>
> ---
>
>
>I am doing select for update in the cursor and coming out of the
procedure without commit/rollback. When I run this procedure from
sqlplus, it should acquire the lock on the rows and when procedure
ends it should release locks as I am closing the cursor.
>
>
>But even if the procedure completes, it doesn't release lock. It
releases locks only if I terminate the session or manually type
rollback or commit on the sqlplus prompt. Can somebody explain this ?
>
>
>Regards,
>
>
>Dilip
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: locks through PL-SQL
Closing a cursor doesn't release the lock.
Think of it in SQL*PLUS. If you issue a SELECT ... FOR UPDATE statment, it
opens an implicit cursor returns the rows and closes the cursor. But it
doesn't release the lock.
Locks can only be released by COMMIT/ROLLBACK
Regards
Naveen
-Original Message-
Sent: Friday, January 03, 2003 4:44 PM
To: Multiple recipients of list ORACLE-L
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
Content-Type: text/plain; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as follows:
---
CREATE OR REPLACE procedure b as
cursor sel_up is select * from emp_info for update;
begin
For emp_rec in sel_up1 LOOP
dbms_output.put_line('Hi');
End loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it should
acquire the lock on the rows and when procedure ends it should release locks
as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com
Buy the best in Movies at http://www.videos.indiatimes.com
Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines
Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
Content-Type: text/html; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as
follows:
---
CREATE OR REPLACE procedure b as
cursor sel_up is select *
from
emp_info for update;
begin
For emp_rec in sel_up1 LOOP
dbms_output.put_line('Hi');
End loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it
should acquire the lock on the rows and when procedure ends it should release
locks as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from
Indiatimes at http://email.indiatimes.com";>http://email.indiatimes.comBuy the
best in Movies at http://www.videos.indiatimes.com";>http://www.videos.indiatimes.com<
br>Now bid just 7 Days in Advance and get
Huge Discounts on Indian Airlines Flights. So log on to http://indianairlines.indiatimes.com";>http://indianairlines.indiatimes.
com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: dilip7772002
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Locks and Waits
There are many things that you could check: 1) size of the shared pool 2) fragmentation of the shared pool 3) free space in the shared pool (together with 2) check also the application for parsing (also soft parses). Check v$rowcache to see access to the rowcache. Anjo. "Reddy, Madhusudana" wrote: > Hello All, > Here is the result I have got from the v$session_event > > SID EVENT > TIME_WAITED > > --- > 22 direct path read > 109 > 20 db file scattered read > 125 > 24 db file scattered read > 160 > 26 SQL*Net more data to client > 162 > 26 db file scattered read > 191 > 26 db file sequential read > 230 > 26 log file sync > 240 > 27 db file sequential read > 398 > 24 db file sequential read > 415 > 22 rdbms ipc reply > 533 > 20 db file sequential read > 603 > 24 log file sync > 813 > 24 latch free > 904 > 20 log file sync > 917 > 27 log file sync > 966 > 26 latch free > 983 > 27 latch free > 2779 > 20 latch free > 3212 > 22 db file scattered read > 4319 > 24 SQL*Net message from client > 5583 > 20 SQL*Net message from client > 6261 > 27 SQL*Net message from client > 7286 > 22 db file sequential read > 8883 > 22 latch free > 16164 > 26 SQL*Net message from client > 56266 > 26 row cache lock > 6487782 > 27 library cache lock > 7433464 > 20 library cache lock > 7433918 > 22 library cache lock > 7435227 > 24 row cache lock > 7435680 > > Could somebody explain me , what are thsese Librarycache Lock and Row cache > lock, and what should I do.. > > I could see lot of locks on the database.. and batch jobs are going very > slow , taking hours ... > > Seems to me like something is happening on database, any idea ??? > > Pl response will be very much appreciated. > > Thanks, > Madhu > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Reddy, Madhusudana > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOCKS - Reading Block Dumps
Riyaj, Thanks for taking the time to provide this information. After running across a few comments from people talking about the need to dump a block to get at the nitty gritty, I decided it was time to explore this area and learn. You and others have been a great help in this effort. And the internals class is something I'm interested in. I hope to get around to taking it sometime. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 04, 2001 4:11 PM To: Multiple recipients of list ORACLE-L Larry fb: indicates the flags.H means header in this rows. F means First piece of the row. L means last piece of the row. Since both FL are present in here there is no row-chaining. lb: lock byte indicates the ITL array index of the transaction that is holding the lock on the row. It is probably one for your test since initrans is 1 for the table. This alone can not determine whether the row is locked or not. This value with the status flag of the transaction from the ITL determines the status of the row lock itself. cc: indicates # of columns and there are 3 columns in this table. Block header portion works differently. scn/fsc: fsc stands for free space credit. fsc is actually have two components. Scn component is copied in to fsc during block cleanout operations. The change that you are seeing is due to fast commit cleanout. Flag indicates the status of the transaction itself. C indicates the commit SCN is copied and U indicates the SCN copied is the upper bound rather than commit SCN itself. So, whether to determine a row is locked or not, here is the logic: 1. Check the lock byte in the row header. 2. If the lock byte is non zero, then go to ITL array entry for that lock byte and check the status of the transaction in the ITL. 0x01 will take to the first member of the ITL array. 3. If the status is C or U then the transaction is committed, then clean up the lock bytes and modify for the current transaction. 4. If the status is null then go to the rollback segment header and try to determine the status of the transaction. 5. If the wrap# of the transaction table entry for the transaction and the transaction in the ITL are the same, then the transaction table entry is not overwritten. Depending upon the transaction status here, enqueue for the resource. If the transaction is committed, then use take the commit SCN of the transaction and update the scn/fsc field. Set the flag to 'C'. 6. If the wrap# of the transaction table entry is higher then the ITL entry wrap#, then use the control SCN to populate the scn/fsc field and set the flag to 'U'. 7. Continue... Hope this helps to understand the concepts..Of course, the internal classes is probably better place to get more information. All right, there may be minor mistakes with this logic, but you get the point, I didn't spend enough time to review this..But the logic is good. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LOCKS - Reading Block Dumps
Larry fb: indicates the flags.H means header in this rows. F means First piece of the row. L means last piece of the row. Since both FL are present in here there is no row-chaining. lb: lock byte indicates the ITL array index of the transaction that is holding the lock on the row. It is probably one for your test since initrans is 1 for the table. This alone can not determine whether the row is locked or not. This value with the status flag of the transaction from the ITL determines the status of the row lock itself. cc: indicates # of columns and there are 3 columns in this table. Block header portion works differently. scn/fsc: fsc stands for free space credit. fsc is actually have two components. Scn component is copied in to fsc during block cleanout operations. The change that you are seeing is due to fast commit cleanout. Flag indicates the status of the transaction itself. C indicates the commit SCN is copied and U indicates the SCN copied is the upper bound rather than commit SCN itself. So, whether to determine a row is locked or not, here is the logic: 1. Check the lock byte in the row header. 2. If the lock byte is non zero, then go to ITL array entry for that lock byte and check the status of the transaction in the ITL. 0x01 will take to the first member of the ITL array. 3. If the status is C or U then the transaction is committed, then clean up the lock bytes and modify for the current transaction. 4. If the status is null then go to the rollback segment header and try to determine the status of the transaction. 5. If the wrap# of the transaction table entry for the transaction and the transaction in the ITL are the same, then the transaction table entry is not overwritten. Depending upon the transaction status here, enqueue for the resource. If the transaction is committed, then use take the commit SCN of the transaction and update the scn/fsc field. Set the flag to 'C'. 6. If the wrap# of the transaction table entry is higher then the ITL entry wrap#, then use the control SCN to populate the scn/fsc field and set the flag to 'U'. 7. Continue... Hope this helps to understand the concepts..Of course, the internal classes is probably better place to get more information. All right, there may be minor mistakes with this logic, but you get the point, I didn't spend enough time to review this..But the logic is good. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com "Larry Elkins" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/04/01 11:36 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: LOCKS - Reading Block Dumps Listers, Playing around with dumping a block to determine rows which are locked. I did an update to a single row in a table and did not commit. I dumped the block. Here is the relevant info: tab 0, row 0, @0x19c3 tl: 21 fb: --H-FL-- lb: 0x1 cc: 3 After rolling back and dumping the block, I get the following: tab 0, row 0, @0x1977 tl: 27 fb: --H-FL-- lb: 0x0 cc: 3 It looks like to me that the "lb:" value indicates the presence of a lock on the row -- 0x1 for a lock, 0x0 for not locked. I've been googling for a bit, searching usenet, and the typical web sites for info on this and came up empty handed. So, can anyone confirm this idea of lb: 0x0 meaning no lock and 0x1 meaning the row is locked? Also, there seems to be some differences in the block header info related to ITL's: Lock present: Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.018.0482 uba: 0x0080c4a8.0340.0e 1 fsc 0x0006. No lock: Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0001.037.04c3 uba: 0x00800107.06dc.31 C--- 0 scn 0x.00594c1b Can I assume that Scn/Fsc value of non-zero means there is a lock? And last but not least, any good info anywhere on reading block dumps? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOCKS - Reading Block Dumps
Chris, And I always thought the answer was 42 ;-) And other than a 3 day gig next week, I've got lots of time on my hands. So, do you want me to dial in or fly out ;-) The reason for asking the question was in relation to a back-channel discussion with someone on locks. I'm familiar with working through the deadlock graph in a trace file, and sometimes using the lock related columns in V$SESSION, as well as all the other lock type of scripts, Metalink notes, etc. But, I had never done a block dump, as some well respected folks recommend, for determining locked rows. Just trying to learn something new. I have since been able to get more info, as well as getting the helpful email from Paul Parker. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Grabowy, > Chris > Sent: Tuesday, December 04, 2001 2:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: LOCKS - Reading Block Dumps > > > Hey Larry, > > With all that free time you got, I got some databases over here > that need to > be upgraded...a couple of developers that need their hands held > (or smacked) > while they code their SQL...and some production tables that need to be > rebuilt...then I can poke around hex dumps of the database(big grin) > > Thanks, your the greatest. Oh, and the answer is 12... > > Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOCKS - Reading Block Dumps
Paul, Thanks for the response -- and a good recommendation, I refer to those sites quite often. I had been searching at the Adams site and browsing Lewis's site prior to sending the email. For whatever reason, I missed/skipped the details on *interpreting* a block dump at the Adams site. I went back later and was able find more info. And the whole reason for this email is discussions by those folks and others about (besides the case of the sometimes useful columns in V$SESSION) doing block dumps to determine locked rows. And the Morle book is on my buy list, just haven't gotten around to it yet. Once again, thanks for your help. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > Sent: Tuesday, December 04, 2001 2:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: LOCKS - Reading Block Dumps > > > Hi Larry, > > the lb (lock byte) in the row piece of the block points to an ITL entry. > > As for the ITL entry, there is a slight confusion in that the > headings don't > line up exactly with values. In your case, when uncommitted, the contents > are : > > > Itl 0x01(ITL no.) > Xid xid: 0x0002.018.0482 (transaction ID) > Uba uba: 0x0080c4a8.0340.0e (undo block address) > Flag (state of > transaction) > Lck 1 (no. of rows > locked by this transaction within this block) > Scn/Fsc fsc 0x0006. (SCN or > Free Space Credit) > > Steve Adams and Jonathan Lewis' sites have some info on block > dumps. Also, > "Scaling Oracle 8i" by James Morle also has some info. > > HTH, > Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOCKS - Reading Block Dumps
Hi Larry, the lb (lock byte) in the row piece of the block points to an ITL entry. As for the ITL entry, there is a slight confusion in that the headings don't line up exactly with values. In your case, when uncommitted, the contents are : Itl 0x01(ITL no.) Xid xid: 0x0002.018.0482 (transaction ID) Uba uba: 0x0080c4a8.0340.0e (undo block address) Flag(state of transaction) Lck 1 (no. of rows locked by this transaction within this block) Scn/Fsc fsc 0x0006. (SCN or Free Space Credit) Steve Adams and Jonathan Lewis' sites have some info on block dumps. Also, "Scaling Oracle 8i" by James Morle also has some info. HTH, Paul -Original Message- Sent: Tuesday, December 04, 2001 12:36 PM To: Multiple recipients of list ORACLE-L Listers, Playing around with dumping a block to determine rows which are locked. I did an update to a single row in a table and did not commit. I dumped the block. Here is the relevant info: tab 0, row 0, @0x19c3 tl: 21 fb: --H-FL-- lb: 0x1 cc: 3 After rolling back and dumping the block, I get the following: tab 0, row 0, @0x1977 tl: 27 fb: --H-FL-- lb: 0x0 cc: 3 It looks like to me that the "lb:" value indicates the presence of a lock on the row -- 0x1 for a lock, 0x0 for not locked. I've been googling for a bit, searching usenet, and the typical web sites for info on this and came up empty handed. So, can anyone confirm this idea of lb: 0x0 meaning no lock and 0x1 meaning the row is locked? Also, there seems to be some differences in the block header info related to ITL's: Lock present: Itl Xid Uba Flag LckScn/Fsc 0x01 xid: 0x0002.018.0482uba: 0x0080c4a8.0340.0e 1 fsc 0x0006. No lock: Itl Xid Uba Flag LckScn/Fsc 0x01 xid: 0x0001.037.04c3uba: 0x00800107.06dc.31 C---0 scn 0x.00594c1b Can I assume that Scn/Fsc value of non-zero means there is a lock? And last but not least, any good info anywhere on reading block dumps? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LOCKS - Reading Block Dumps
Hey Larry, With all that free time you got, I got some databases over here that need to be upgraded...a couple of developers that need their hands held (or smacked) while they code their SQL...and some production tables that need to be rebuilt...then I can poke around hex dumps of the database(big grin) Thanks, your the greatest. Oh, and the answer is 12... Chris -Original Message- Sent: Tuesday, December 04, 2001 12:36 PM To: Multiple recipients of list ORACLE-L Listers, Playing around with dumping a block to determine rows which are locked. I did an update to a single row in a table and did not commit. I dumped the block. Here is the relevant info: tab 0, row 0, @0x19c3 tl: 21 fb: --H-FL-- lb: 0x1 cc: 3 After rolling back and dumping the block, I get the following: tab 0, row 0, @0x1977 tl: 27 fb: --H-FL-- lb: 0x0 cc: 3 It looks like to me that the "lb:" value indicates the presence of a lock on the row -- 0x1 for a lock, 0x0 for not locked. I've been googling for a bit, searching usenet, and the typical web sites for info on this and came up empty handed. So, can anyone confirm this idea of lb: 0x0 meaning no lock and 0x1 meaning the row is locked? Also, there seems to be some differences in the block header info related to ITL's: Lock present: Itl Xid Uba Flag LckScn/Fsc 0x01 xid: 0x0002.018.0482uba: 0x0080c4a8.0340.0e 1 fsc 0x0006. No lock: Itl Xid Uba Flag LckScn/Fsc 0x01 xid: 0x0001.037.04c3uba: 0x00800107.06dc.31 C---0 scn 0x.00594c1b Can I assume that Scn/Fsc value of non-zero means there is a lock? And last but not least, any good info anywhere on reading block dumps? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locks
spool holding_session.lst column object_name justify c heading "Object|Name" format a32 column usernamejustify c heading "User|Name"format a7 column osuser justify c heading "OS|User" format a7 column pid justify c heading "Ora|Proc|ID" format 999 column serial# justify c heading "Ora|Serial|#" format 99 column sid justify c heading "Holding|Session" format 999 column spidjustify c heading "Unix|Proc"format a5 column object_id justify c heading "Obj|ID" format 9 column lockwaitjustify c heading "Lock|Wait" column typejustify c heading "Lock|Type"format a4 column lmode justify c heading "Mode" format set pagesize 60 linesize 100 select lck.sid, ses.serial#, pro.pid, pro.spid, obj.object_name, obj.object_id, ses.username, ses.osuser, lck.type, lck.lmode from dba_blockers blk, dba_objects obj, v$lock lck, v$session ses, v$process pro where blk.holding_session = ses.sid and lck.id1 = obj.object_id and lck.sid = ses.sid and ses.paddr = pro.addr and ses.username is not NULL order by obj.object_name, ses.lockwait desc, lck.sid; spool off clear columns Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 21 Jun 2001 13:36:00 -0800 Does anyone have any script that will help me find out the locks that are held in the database. We are facing a problem related to lock.One of the transaction is holding a lock on a table and there is another transaction wanting a lock on that table but waits for long time .Is there any lock timeout parameter that can be set on the database. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locks
"Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Thursday, June 21, 2001 5:36 PM To: Multiple recipients of list ORACLE-L Does anyone have any script that will help me find out the locks that are held in the database. We are facing a problem related to lock.One of the transaction is holding a lock on a table and there is another transaction wanting a lock on that table but waits for long time .Is there any lock timeout parameter that can be set on the database. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). waiters.sql whatslocked.sql locks.sql
Re: Locks
This one showed up on the list a couple of weeks ago. It has heaps of good diagnostic info. set linesize 132 pagesize 66 break on Kill on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 990 column id2 format 990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 9 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a35 heading "Table Name" column owner format a9 column Address format a18 select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID||','||S.SERIAL# Kill, U1.NAME||'.'||substr(T1.NAME,1,20) tab, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5 / Ravindra Basavaraja wrote: Does anyone have any script that will help me find out the locks that are held in the database. We are facing a problem related to lock.One of the transaction is holding a lock on a table and there is another transaction wanting a lock on that table but waits for long time .Is there any lock timeout parameter that can be set on the database. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Glen Mitchell NZ Phone: +64 9 3730400 Energy Research Lab URL: http://www.peace.com Peace Software Email: [EMAIL PROTECTED]
