Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle query problem

Posted on 2001-07-03
11
Medium Priority
?
641 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 200 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

879 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