Identify Locked objects that are Invalid

Here is a query that is useful to identify locks on objects that are Invalid, preventing the object from being recompiled. These sessions need to be identified and stopped or killed.

with src as
(
    select sid, ao.owner, object, status
                 from v$access v, all_objects ao
                where ao.object_name = v.object
                  and ao.status= 'INVALID'
                )
select v.sid,
       src.owner,
       src.object
       serial#,
       username,
       osuser,
       logon_time,
       program,
       action,
       src.status
  from v$session v, src
where v.sid = src.sid;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: