Solved

Oracle query problem

Posted on 2001-07-03
11
623 Views
Last Modified: 2007-12-19
 Problem : I have two front end forms open in two seperate place, both are far distant.Two seperate user has logged on the both forms.both user has queried same record.If one tries to update the record he finds it locked because it is opened by other.And he does not know which user has locked the record.Here is the case of two user.But in real time application may be many user accessing same record.Can you tell any way or by oracle query to get how many users with name has locked the record.
waiting for answer.

0
Comment
Question by:Sinha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 

Author Comment

by:Sinha
ID: 6248682
Hi guys,
 can you help me out for this problem.
sinha
0
 
LVL 2

Accepted Solution

by:
jammalk earned 50 total points
ID: 6248781
V$LOCKED_OBJECT data dictionary view gives the required info. Go to the following sites to know more about V$LOCKED_OBJECT & V$LOCK definitions & comments.

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/ch383.htm#46175


http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/ch380.htm#5397

Cheers
0
 

Author Comment

by:Sinha
ID: 6248883
HI,
    these tables are showing oracle username and OS user name but not the application name , it is possible that these same users may lock another application because application name is not known.Can i know in better form like application name,lockeduser name,lockedos usename in table form.Pls help by giving a query which i will run on my system
rgds
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Sinha
ID: 6248927
HI,
    these tables are showing oracle username and OS user name but not the application name , it is possible that these same users may lock another application because application name is not known.Can i know in better form like application name,lockeduser name,lockedos usename in table form.Pls help by giving a query which i will run on my system
rgds
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6248932
Replace the objectname TEST with the table locked in your application..!

select sid, serial#, username, osuser, machine, terminal, program
from v$session
where (username, sid) = (select oracle_username, session_id from v$locked_object
                    where object_id= (select object_id from user_objects
                             where object_name='TEST'
                             and object_type='TABLE'))
/

Hope it helps!
Jammalk
0
 

Author Comment

by:Sinha
ID: 6249041
Thanks ,
     Will it work for all type of lock. My application's base table which is locked is not showing in the list of v$locked_object.what kind of lock this table contains.
Actually while updating the data from one form i am getting message "Could not reserve record(2 tries) Keep trying".Is this a lock, if it is a lock why it's base table is showing in the V$locked_object.Pl.s help me.
if any clarification pls. revert i will mail imme.
Thanks & rgds
sinha
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6249080
Yep..! It works for any type of locks..!
In fact, the column LOCED_MODE in the table V$LOCKED_OBJECT has the following possibilities:

0, None
1, Null (NULL)
2, Row-S (SS)
3, Row-X (SX)
4, Share (S)
5, S/Row-X (SSX)
6, Exclusive (X)

To test whether the lock is visible in V$LOCKED_OBJECT or not..., do the following:
   1. Update a record in your front end.
   2. Dont commit.
   3. Run the above mentioned query on the object you are looking for...!
If you dont see the object.., it may be because.. the tablename must have been mixed/lower case..! Make sure the table name is  uppercase.. in the above query..!
Let me know the result!
jammalk

0
 
LVL 2

Expert Comment

by:AlbertYou
ID: 6251076
Hi:

Try to modify the query

select sid, serial#, username, osuser, machine, terminal, program
from v$session
where (username, sid) = (select oracle_username, session_id from v$locked_object
                   where object_id= (select object_id from ALL_objects
                            where object_name='TEST'
                            and object_type='TABLE'))


Note : Replace USER_OBJECTS by ALL_OBJECTS if the user is not the owner of the target table )

0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7038054
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7052604
Recommended disposition:
    Accept jammalk's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7052800
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question