RE: Locking tables in Oracle
V$LOCKED_OBJECT This view lists all locks acquired by every transaction on the system. Column Datatype Description XIDUSN NUMBER Undo segment number XIDSLOT NUMBER Slot number XIDSQN NUMBER Sequence number OBJECT_ID NUMBER Object ID being locked SESSION_ID NUMBER Session ID ORACLE_USERNAME VARCHAR2(30) Oracle user name OS_USER_NAME VARCHAR2(15) OS user name PROCESS VARCHAR2(9) OS process ID LOCKED_MODE NUMBER Lock mode -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]]Sent: Thursday, March 27, 2003 3:14 PMTo: Multiple recipients of list ORACLE-LSubject: Locking tables in OracleHi All, Does anybody has any insight of dynamic view v$locked_object? It populates its locked_mode column with numbers what does those numbers mean?TIA,Rajesh--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Pillai, Rajesh INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Locking tables in Oracle
Mladen, What does it mean if locked_mode column is populated with 0 or 3 or 6. What kind of locking does it shows (RS, X etc...)? Thanks a lot, Rajesh -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]Sent: Thursday, March 27, 2003 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Locking tables in Oracle V$LOCKED_OBJECT This view lists all locks acquired by every transaction on the system. Column Datatype Description XIDUSN NUMBER Undo segment number XIDSLOT NUMBER Slot number XIDSQN NUMBER Sequence number OBJECT_ID NUMBER Object ID being locked SESSION_ID NUMBER Session ID ORACLE_USERNAME VARCHAR2(30) Oracle user name OS_USER_NAME VARCHAR2(15) OS user name PROCESS VARCHAR2(9) OS process ID LOCKED_MODE NUMBER Lock mode -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]]Sent: Thursday, March 27, 2003 3:14 PMTo: Multiple recipients of list ORACLE-LSubject: Locking tables in OracleHi All, Does anybody has any insight of dynamic view v$locked_object? It populates its locked_mode column with numbers what does those numbers mean?TIA,Rajesh--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Pillai, Rajesh INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Locking tables in Oracle
Rajesh, 2 -- ROW SHARE or SHARE UPDATE 3 -- ROW EXCLUSIVE 4 -- SHARE 5 -- SHARE ROW EXCLUSIVE 6 -- EXCLUSIVE Mike -Original Message- Sent: Thursday, March 27, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Hi All, Does anybody has any insight of dynamic view v$locked_object? It populates its locked_mode column with numbers what does those numbers mean? TIA, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: Hand, Michael T 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: Locking tables in Oracle
SELECT OWNER||'.'||OBJECT_NAME Object, OS_USER_NAME Terminal, ORACLE_USERNAME Locker, PROGRAM Program, NVL(lockwait,'ACTIVE') Wait, DECODE(LOCKED_MODE, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') Lockmode, OBJECT_TYPE Object Type, SESSION_ID Session ID, SERIAL# Serial, c.SID FROM SYS.V_$LOCKED_OBJECT A, SYS.ALL_OBJECTS B, SYS.V_$SESSION c WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID ORDER BY 1 ASC, 5 Desc -Original Message- Sent: Thursday, March 27, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Hi All, Does anybody has any insight of dynamic view v$locked_object? It populates its locked_mode column with numbers what does those numbers mean? TIA, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: Arun Chakrapanirao 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: Locking tables in Oracle
Dynamic Performance (V$) Views, 95 of 237 V$LOCK This view lists the locks currently held by the Oracle server and outstanding requests for a lock or latch. Column Datatype Description ADDR RAW(4) Address of lock state object KADDR RAW(4) Address of lock SID NUMBER Identifier for session holding or acquiring the lock TYPE VARCHAR2(2) Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: TM - DML enqueue TX - Transaction enqueue UL - User supplied The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table3-1. ID1 NUMBER Lock identifier #1 (depends on type) ID2 NUMBER Lock identifier #2 (depends on type) LMODE NUMBER Lock mode in which the session holds the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) REQUEST NUMBER Lock mode in which the process requests the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) CTIME NUMBER Time since current mode was granted BLOCK NUMBER The lock is blocking another lock Table 3-1 Values for the TYPE Column: System Types System Type Description System Type Description BL Buffer hash table instance NA..NZ Library cache pin instance (A..Z = namespace) CF Control file schema global enqueue PF Password File CI Cross-instance function invocation instance PI, PS Parallel operation CU Cursor bind PR Process startup DF Data file instance QA..QZ Row cache instance (A..Z = cache) DL Direct loader parallel index create RT Redo thread global enqueue DM Mount/startup db primary/secondary instance SC System commit number instance DR Distributed recovery process SM SMON DX Distributed transaction entry SN Sequence number instance FS File set SQ Sequence number enqueue HW Space management operations on a specific segment SS Sort segment IN Instance number ST Space transaction enqueue IR Instance recovery serialization global enqueue SV Sequence number value IS Instance state TA Generic enqueue IV Library cache invalidation instance TS Temporary segment enqueue (ID2=0) JQ Job queue TS New block allocation enqueue (ID2=1) KK Thread kick TT Temporary table enqueue LA .. LP Library cache lock instance lock (A..P = namespace) UN User name MM Mount definition global enqueue US Undo segment DDL MR Media recovery WL Being-written redo log instance-Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]Sent: Thursday, March 27, 2003 4:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Locking tables in Oracle Mladen, What does it mean if locked_mode column is populated with 0 or 3 or 6. What kind of locking does it shows (RS, X etc...)? Thanks a lot, Rajesh -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]Sent: Thursday, March 27, 2003 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Locking tables in Oracle V$LOCKED_OBJECT This view lists all locks acquired by every transaction on the system. Column Datatype Description XIDUSN NUMBER Undo
RE: Locking tables in Oracle
Title: RE: Locking tables in Oracle -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]] What does it mean if locked_mode column is populated with 0 or 3 or 6. What kind of locking does it shows (RS, X etc...)? I know someone has already answered this, but you can find the official answer in the documentation. Look up the description of v$lock in the Server Reference Manual. From the 9.2 manual (Oracle9i Database Reference Release 2 (9.2) Part Number A96536-02), description of column v$lock.lmode: LMODE NUMBER Lock mode in which the session holds the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X)
RE: locking issues in web based applications
Dennis , thanks for your suggestions. It seems detection is the only viable option to avoid locking. i'll try to get the suggested book from somewhere . ratnesh -Original Message- WILLIAMS Sent: Tuesday, July 23, 2002 9:08 PM To: Multiple recipients of list ORACLE-L Ratnesh I assume you are using the JDBC interface, rather than J2EE. You may want to consider buying the book Java Programming with Oracle JDBC by Donald Bales. He devotes a chapter to this subject. One issue he raises that will limit your options is whether you have other applications besides your Web-based application accessing this database. If not, that gives you additional flexibility. Bales distinguishes between locking and detection. He contends that locking alone does not solve the problem of multiuser data access integrity, and offers several examples to support his contention. He then outlines 3 methods for employing detection. He defines detection as the ability to detect if data you are about to modify has changed since the point when you selected it to be updated. 1. Pessimistic. Use an updatestamp 2. Pessimistic. Compare all the columns in the table or attributes of an object with their original values. 3. Optimistic. Compare only modified columns or attributes in a WHERE clause. I wish I could speak from experience, but I think this author has studied this issue in more detail than I could. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 23, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Hi I'm not very familiar with web based application development. One of our programmers has asked me a question regarding locking issues in web based applications.In a typical scenario , users access the oracle database thro a browser via app-svr/web-svr . Connection pooling is implemented on the web-svr to support large no of users. If a user locks some objects via updates or deletes , then kills his browser , the objects remains locked. The question is how to design the system such that abnormal client browser termination does not lock any objects.One solution is not to lock objects at all , and commit immediately after update if the object timestamp has not changed .But this approach is suitable only for short sweet transactions. Connection timeout is too time-taking for intensive applications to be of any use. There must be other better ways of doing this. I need your suggestions. TIA, ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: DENNIS WILLIAMS 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: Ratnesh Kumar Singh 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: locking issues in web based applications
Ratnesh I assume you are using the JDBC interface, rather than J2EE. You may want to consider buying the book Java Programming with Oracle JDBC by Donald Bales. He devotes a chapter to this subject. One issue he raises that will limit your options is whether you have other applications besides your Web-based application accessing this database. If not, that gives you additional flexibility. Bales distinguishes between locking and detection. He contends that locking alone does not solve the problem of multiuser data access integrity, and offers several examples to support his contention. He then outlines 3 methods for employing detection. He defines detection as the ability to detect if data you are about to modify has changed since the point when you selected it to be updated. 1. Pessimistic. Use an updatestamp 2. Pessimistic. Compare all the columns in the table or attributes of an object with their original values. 3. Optimistic. Compare only modified columns or attributes in a WHERE clause. I wish I could speak from experience, but I think this author has studied this issue in more detail than I could. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 23, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Hi I'm not very familiar with web based application development. One of our programmers has asked me a question regarding locking issues in web based applications.In a typical scenario , users access the oracle database thro a browser via app-svr/web-svr . Connection pooling is implemented on the web-svr to support large no of users. If a user locks some objects via updates or deletes , then kills his browser , the objects remains locked. The question is how to design the system such that abnormal client browser termination does not lock any objects.One solution is not to lock objects at all , and commit immediately after update if the object timestamp has not changed .But this approach is suitable only for short sweet transactions. Connection timeout is too time-taking for intensive applications to be of any use. There must be other better ways of doing this. I need your suggestions. TIA, ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: DENNIS WILLIAMS 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: Locking Issue
Yes. If your application executes a DML statement and then just hangs around without commiting or doing a rollback this will occur. A user forgetting to commit or rollback from SQL*Plus is often the cause of locking problems. Bill --- Hamid Alavi [EMAIL PROTECTED] wrote: List, I am monitoring a locking issue on database(oracle 8.1.7.0 on sun ), when I look at all active session there are no active session, but the same time there are two active rollback segment assign to two session, my question is can we have an inactive session with an active rollback segment? Thanks, Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass 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: Locking Issue
Yes. -Original Message- From: Hamid Alavi [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 11, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Subject: Locking Issue List, I am monitoring a locking issue on database(oracle 8.1.7.0 on sun ), when I look at all active session there are no active session, but the same time there are two active rollback segment assign to two session, my question is can we have an inactive session with an active rollback segment? Thanks, Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Gogala, Mladen 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: Locking issue
ORA-2 is an error raised by raise_application_error. In other words, you must examine the code, as it is a custom error message. Jared On Tuesday 04 September 2001 07:25, Libal, Ivo wrote: Hello All we got a problem with DBMS_ALERT package. On clients we get sometimes an error message: ORA-2: ORU-10001: lock request error, status: 2 Where can be a problem? Is it a problem of ENQUEUE_RESOURCES or DML_LOCKS parameters? How can I check it? Thank you for any responce Regards Ivo Libal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Locking issue
Jared, wish that was entirely true, look at $ORACLE_HOME/rdbms/admin/dbmsutil.sql some of oracle's code is using low numbered 2 error nums :((at least thats that the internal docs say) joe [EMAIL PROTECTED] 09/04/01 12:32PM ORA-2 is an error raised by raise_application_error.In other words, you must examine the code, as it isa custom error message.JaredOn Tuesday 04 September 2001 07:25, Libal, Ivo wrote: Hello All we got a problem with DBMS_ALERT package. On clients we get sometimes an error message: ORA-2: ORU-10001: lock request error, status: 2 Where can be a problem? Is it a problem of ENQUEUE_RESOURCES or DML_LOCKS parameters? How can I check it? Thank you for any responce Regards Ivo Libal-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Jared Still INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Fwd: RE: Locking issue
Ivo, I can't find a reference to any 'ORU' errors in the docs or on MetaLink. Since it was reported via raise_application_error(), I still think you should look at the code, if available. If the code is not available, open an iTar at MetaLink, unless someone else on the list can tell you what this is. Jared To: [EMAIL PROTECTED] cc: Subject: Fwd: RE: Locking issue -- Forwarded Message -- Date: Tue, 4 Sep 2001 17:41:19 +0200 To: Jared Still [EMAIL PROTECTED] The error is raised inside DBS_ALERT package (wrapped). I would like to know what can cause the ORU-10001: lock request error, status: 2 error message when using dbms_alert? Could it be a problem of insufficient resources / dml_locks? Thank you for responce Ivo -Original Message- Sent: Tuesday, September 04, 2001 05:31 PM To: [EMAIL PROTECTED]; Libal, Ivo ORA-2 is an error raised by raise_application_error. In other words, you must examine the code, as it is a custom error message. Jared On Tuesday 04 September 2001 07:25, Libal, Ivo wrote: Hello All we got a problem with DBMS_ALERT package. On clients we get sometimes an error message: ORA-2: ORU-10001: lock request error, status: 2 Where can be a problem? Is it a problem of ENQUEUE_RESOURCES or DML_LOCKS parameters? How can I check it? Thank you for any responce Regards Ivo Libal --- -- 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: Fwd: RE: Locking issue
This message comes from DBMS_ALERT_INFO package body. You need to contact OWS. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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: Fwd: Re: Locking issue
Thanks Joe, I had forgotten about that. Jared Jared Still jkstill@cybco To: [EMAIL PROTECTED] n.com cc: Subject: Fwd: Re: Locking issue 09/04/01 09:05 AM -- Forwarded Message -- Date: Tue, 04 Sep 2001 12:06:16 -0400 To: [EMAIL PROTECTED], [EMAIL PROTECTED] Jared, wish that was entirely true, look at $ORACLE_HOME/rdbms/admin/dbmsutil.sql some of oracle's code is using low numbered 2 error nums :((at least thats that the internal docs say) joe [EMAIL PROTECTED] 09/04/01 12:32PM ORA-2 is an error raised by raise_application_error. In other words, you must examine the code, as it is a custom error message. Jared On Tuesday 04 September 2001 07:25, Libal, Ivo wrote: Hello All we got a problem with DBMS_ALERT package. On clients we get sometimes an error message: ORA-2: ORU-10001: lock request error, status: 2 Where can be a problem? Is it a problem of ENQUEUE_RESOURCES or DML_LOCKS parameters? How can I check it? Thank you for any responce Regards Ivo Libal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Locking Issue
Kevin,
Here's one that has been posted to our site recently by a customer
(www.cool-tools.co.uk Support User Defined Collections BLOCKER):
select l.sid sid,
s.username username,
s.program program,
t.sql_text,
u.name owner,
o.name object,
l.type type,
lmode,
decode (lmode,1,'NULL',2,'Row Share',3,'Row
Exclusive',4,'Share',5,'Share Row',6,'Exclusive')mode_desc,
request,
decode (request,1,'NULL',2,'Row Share',3,'Row
Exclusive',4,'Share',5,'Share Row',6,'Exclusive')request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW','TM','TX','UL')
and l.sid=s.sid(+)
and l.id1 = o.obj# (+)
and o.owner#=u.user#(+)
and s.sql_hash_value = t.hash_value
and lmode 0
The only problem with running this is that you still have a chance that you
are not going to catch the offending statement, as it could have been aged
out of the SQLAREA., or the offending user/session may have moved on to
another transaction.
There was a thread about this on the list a little while ago, and it is
quite hard (impossible) to *Guarantee* catching the offending statements. We
are five nines sure that we can do this now though, unless in extreme
cases where a user takes a lock out - when nobody else is accessing the
system - and half an hour later somebody tries to access the table, and the
lock has not been released. What we now do is run the rule every 1-2
minutes to monitor for blocked sessions.
You could most probably run a cron job to fire this statement off every 1
minute or so, and should be able to get to the bottom of the problem from
there. Personally I would not leave this statement at such a low refresh
interval continuously though, and I can't help with cron or as I haven't got
a clue how to use it :P
HTH
Mark
-Original Message-
Sent: Thursday, August 23, 2001 17:06
To: Multiple recipients of list ORACLE-L
Thanks Christopher. I will see what I can get out of them.
-Original Message-
Sent: Thursday, August 23, 2001 10:24 AM
To: Multiple recipients of list ORACLE-L
Take a look at www.vampired.net under scripts and locks, there are a few
decent scripts there.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Wednesday, August 22, 2001 2:47 PM
To: Multiple recipients of list ORACLE-L
Back to the experts
We have an application that, litterally overnight, developed locking issues.
Our users start working just fine. Then, right now cause unknown, our
inserts start being blocked. Usually there are so many so fast that we do
not even know what is causing the original lock.
The tables the users are being blocked from have multiple triggers and the
tables that these triggers point to ofen have triggers of their own.
So, the scenario is
1. Web Application pointing to an Oracle 8.0.5 database.
2. Things start out just fine.
3. At a variable time later (i.e. not the same time of day, not the same
cumulated time since startup, etc.) locks start to appear against our main
table. The users getting the locks are trying to insert a record.
4. Locks cascade until the only recourse is to restart the database.
We are having trouble tracing the locks back to their origin.
There has got to be 1 thing that is causing the start of this cascade.
If anyone has any insights on how to find this one cause ... I would
appreciate hearing them.
And , if you happen to have any scripts that would help me trace the locks
thru the database I would appreciate them also.
Thanks
Kevin
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
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: Locking Issue
Title: Message Take a look at www.vampired.net under scripts and locks, there are a few decent scripts there. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Kevin Lange [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 22, 2001 2:47 PMTo: Multiple recipients of list ORACLE-LSubject: Locking Issue Back to the experts We have an application that, litterally overnight, developed locking issues. Our users start working just fine. Then, right now cause unknown, our inserts start being blocked. Usually there are so many so fast that we do not even know what is causing the original lock. The tables the users are being blocked from have multiple triggers and the tables that these triggers point to ofen have triggers of their own. So, the scenario is 1. Web Application pointing to an Oracle 8.0.5 database. 2. Things start out just fine. 3. At a variable time later (i.e. not the same time of day, not the same cumulated time since startup, etc.) locks start to appear against our main table. The users getting the locks are trying to insert a record. 4. Locks cascade until the only recourse is to restart the database. We are having trouble tracing the locks back to their origin. There has got to be 1 thing that is causing the start of this cascade. If anyone has any insights on how to find this one cause ... I would appreciate hearing them. And , if you happen to have any scripts that would help me trace the locks thru the database I would appreciate them also. Thanks Kevin
RE: Locking Issue
Title: Message Thanks Christopher. I will see what I can get out of them. -Original Message-From: Christopher Spence [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 23, 2001 10:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Locking Issue Take a look at www.vampired.net under scripts and locks, there are a few decent scripts there. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Kevin Lange [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 22, 2001 2:47 PMTo: Multiple recipients of list ORACLE-LSubject: Locking Issue Back to the experts We have an application that, litterally overnight, developed locking issues. Our users start working just fine. Then, right now cause unknown, our inserts start being blocked. Usually there are so many so fast that we do not even know what is causing the original lock. The tables the users are being blocked from have multiple triggers and the tables that these triggers point to ofen have triggers of their own. So, the scenario is 1. Web Application pointing to an Oracle 8.0.5 database. 2. Things start out just fine. 3. At a variable time later (i.e. not the same time of day, not the same cumulated time since startup, etc.) locks start to appear against our main table. The users getting the locks are trying to insert a record. 4. Locks cascade until the only recourse is to restart the database. We are having trouble tracing the locks back to their origin. There has got to be 1 thing that is causing the start of this cascade. If anyone has any insights on how to find this one cause ... I would appreciate hearing them. And , if you happen to have any scripts that would help me trace the locks thru the database I would appreciate them also. Thanks Kevin
Re: Locking Issue
check out $ORACLE_HOME/rdbms/admin/utlockt.sql joe [EMAIL PROTECTED] 08/22/01 02:46PM Back to the experts We have an application that, litterally overnight, developed locking issues. Our users start working just fine. Then, right now cause unknown, our inserts start being blocked. Usually there are so many so fast that we do not even know what is causing the original lock. The tables the users are being blocked from have multiple triggers and the tables that these triggers point to ofen have triggers of their own. So, the scenario is 1. Web Application pointing to an Oracle 8.0.5 database. 2. Things start out just fine. 3. At a variable time later (i.e. not the same time of day, not the same cumulated time since startup, etc.) locks start to appear against our main table. The users getting the locks are trying to insert a record. 4. Locks cascade until the only recourse is to restart the database. We are having trouble tracing the locks back to their origin. There has got to be 1 thing that is causing the start of this cascade. If anyone has any insights on how to find this one cause ... I would appreciate hearing them. And , if you happen to have any scripts that would help me trace the locks thru the database I would appreciate them also. Thanks Kevin
RE: Locking
Try with this query: select xidusn, object_id, session_id, locked_mode from v$locked_object; If developer created table with bitmap index it can be reason for locking (we had such problem). HTH, Sonja -Original Message- Sent: Tuesday, March 20, 2001 11:27 PM To: Multiple recipients of list ORACLE-L Recently, one of my users nor I could update a table. As I dug around, I couldn't find any of the 'lock' tables such as dba_locks. Eventually, I managed to start data gatherer and get OEM's lock manager to kill the hung session. What do you guys do to find and terminate this deadlock or hung sessions? Are the 'lock' system tables built with specific options during db build? TIA. ...R [EMAIL PROTECTED] _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Lau 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: =?windows-1250?Q?Sonja_=8Aehovi=E6?= 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: locking tables
Hi Austin I dont know wether it can be done from the DB or not... But you can do it from your application by setting a flag when ever an update is happening and selecting only those records which doesn't hjave this flag set. Regards, Mohan Kris Austin wrote: hi list, how can you prevent another session from selecting from the table until your update has committed? we have a procedure that is querying and updating the table as one transaction. theoretically, we'd like to prevent any other session from accessing that table. is this possible? we cannot limit the oracle account to one session. many sessions need to be open since this is an ecommerce database application. we are running oracle 8.1.6 on solaris. thanks! kris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kris Austin 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: Ramamohan B N 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: locking tables
You can lock a table with: lock table scott.emp in exclusive mode This should lock that table for the session you are in, not sure what permissions you need for it though? Mark -Original Message- Sent: Monday, February 12, 2001 07:31 To: Multiple recipients of list ORACLE-L hi list, how can you prevent another session from selecting from the table until your update has committed? we have a procedure that is querying and updating the table as one transaction. theoretically, we'd like to prevent any other session from accessing that table. is this possible? we cannot limit the oracle account to one session. many sessions need to be open since this is an ecommerce database application. we are running oracle 8.1.6 on solaris. thanks! kris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kris Austin 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: Mark Leith 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: locking tables
Kris: There is no native way of doing this. You would need to write your own data-access methods -- maybe a stored procedure that returns a ref-cursor. The procedure would attempt to lock a table before performing the select. The DML operations on the table would have to lock the same table before the operation can proceed. Hope this helps Kevin -Original Message- Sent: Monday, February 12, 2001 2:31 PM To: Multiple recipients of list ORACLE-L hi list, how can you prevent another session from selecting from the table until your update has committed? we have a procedure that is querying and updating the table as one transaction. theoretically, we'd like to prevent any other session from accessing that table. is this possible? we cannot limit the oracle account to one session. many sessions need to be open since this is an ecommerce database application. we are running oracle 8.1.6 on solaris. thanks! kris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kris Austin 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: Toepke, Kevin M 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).
