regarding osuser

thota198
thota198 used Ask the Experts™
on
Hi
     i wanted to check the user who locked certain objects in the proc i used the query
'select sid, osuser from v$session' it showed osuser as oracle. im not dba im only developer how do i find the name of the user whos using it i was able to find my userid in v$session but not the user who  locked few objects tht i currently want to work on  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
are you able to exeucte the following query?

select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
There are a TON of scripts out there that show object locks:

Look at the examples here:
http://psoug.org/reference/locks.html
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
@ajexpert:
      im able to generate the query given by u but my doubt is when i query why is it showing the osuser as oracle when i check my session id it shows my userid, i mean when i query, it did show few userids with session ids but for some of them it showed it as 'oracle'  , i didnt understand tht
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I has been years since I had to track this down but I believe the osuser being 'oracle' means it is a background process.

I would not pay that much attention to this.  Look for the locking SQL then track down what is causing it.

Author

Commented:
the locked objects are tables and how to track down what is causing it

Commented:
adding to what lightwv said:

Yes it could be background process, which normallly happen at server.

You should also check the machine where it is being executed and you shall get the idea.

Ideally, to resolve it, you should kill all these processes

Commented:
>>the locked objects are tables and how to track down what is causing it

you have to identify the osusername, machine, terminal, object id's causing these table locks

If you get info on all of these above, you should be able to track down who and what is causing it

HTH
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Again,  there are many scripts out there to track down locks.

Have you looked around and tried any other than the sql explicitly posted here?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I just remembered I came across a great script a while back searching for something else.  On mobile and cannot find it right now but search around for oracle blocking locks or oracle what is locking my sql.

One of those should find it.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>may be this?

Not sure.  I do like Burleson scripts so it could be.

I think I saved it to my 'tools' folder.  If necessary I'll see if I can find it tomorrow.

I shouldn't have to since there are many similar scripts around with some quick searches.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
ajexpert,

That was it.  Same script, different link:
http://www.oracle-base.com/dba/Script.php?category=10g&file=lock_tree.sql

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial