Avatar of thota198
thota198
 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  
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question