Solved

Oracle query problem

Posted on 2001-07-03
11
617 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
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: 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Parse String 5 47
Oracle SQL Select unique values from two columns 4 66
Select and Insert Query running slow 4 48
SQL Workhours Count beetween Workhours 3 26
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

820 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