Solved

Oracle query problem

Posted on 2001-07-03
11
629 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

630 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