asked on

regarding osuser

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

ajexpert

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

slightwv (䄆 Netminder)

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

Look at the examples here:
http://psoug.org/reference/locks.html

ajexpert

another good link
http://forums.oracle.com/forums/thread.jspa?threadID=1133280

thota198

ASKER

@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

slightwv (䄆 Netminder)

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.

thota198

ASKER

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

ajexpert

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

ajexpert

>>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

slightwv (䄆 Netminder)

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?

slightwv (䄆 Netminder)

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.

ajexpert

may be this?

http://www.dba-oracle.com/t_locked_rows_user_locks.htm

slightwv (䄆 Netminder)

>>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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial