Solved

Oracle query problem

Posted on 2001-07-03
11
603 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now