CODEWITHSUNDEEP
sqloracle-databaselocking
Jdzel
Jdzel

Reputation: 159

How to find the timestamp of the last lock?

I want to determine which process locks my table TABLE1, and the timestamp when it was locked.

I suppose that I should use tables like dba_locks and gv$session, but I'm new on Oracle and I don't know much about system tables.

Can anybody help me with this query?

Upvotes: 1

Views: 17960

Answers (2)

Jon Heller
Jon Heller

Reputation: 36902

Use this query to find the last time a session was blocked waiting for access to a specific table:

select max(sample_time) last_block_time
from gv$active_session_history
--Or use this table for further back.
--from dba_hist_active_sess_history
where blocking_session is not null
    and current_obj# =
    (
        select object_id
        from dba_objects
        where owner = 'JHELLER'       --Enter the object owner here.
            and object_name = 'TEST1' --Enter the object name here.
    );

This is not necessarily the same thing as "when was the last time the table was locked". It's possible the table, or a row in the table, was locked but no sessions waited on it. And it's possible that a session did wait on it but not during the sample.

However, if something does not happen often enough to appear in the session history tables then it's usually not important enough to worry about.

Upvotes: 3

atokpas
atokpas

Reputation: 3351

You can query V$LOCKED_OBJECT to get information about currently locked objects.

SQL> desc v$locked_object;
 Name                  Null?    Type
 -------------------- -------- ----------------------------
 XIDUSN                         NUMBER
 XIDSLOT                        NUMBER
 XIDSQN                         NUMBER
 OBJECT_ID                      NUMBER
 SESSION_ID                     NUMBER
 ORACLE_USERNAME                    VARCHAR2(30)
 OS_USER_NAME                       VARCHAR2(30)
 PROCESS                        VARCHAR2(24)
 LOCKED_MODE                        NUMBER

Oracle doesnt keep history of locks. However you can you can query DBA_HIST_ACTIVE_SESS_HISTORY amd V$ACTIVE_SESSION_HISTORY to get information about locking session but not the locked tables.

Upvotes: 1

Related Questions