Locked objects, Stored Procedures, Triggers, etc

pts.
Tags:
Migration
Oracle
Oracle administration
We have a custom migration system for Oracle Objects but it simply tries to create for replace the ojbect. Sometimes we are unable to do the create or replace because a program is running utilizing the object for someone us running a script or SQL statment utilizing the object. Is there a way to determine in Oracle who or what program has the object locked and then back track it to a sid or unix process? This question is less tables, I can determine who has that locked.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The following SQL will help:

select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS'
/

:?

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • OUTDATED
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/locked-objects-stored-procedures-triggers-etc/ (0) Comments Read [...]
    0 pointsBadges:
    report
  • hah

    select

    object_name, s.sid, s.serial#, p.spid,l.OS_USER_NAME,o.OBJECT_TYPE

    from

    v$locked_object l, dba_objects o, v$session s, v$process p

    where

    l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following