LIFE@ONLYRING » lock on objects

Posts Tagged ‘lock on objects’

Script to Query on Current Transactions and Table Locks

June 10th, 2008 by onlyring | No Comments »

By Jeff Hunter, ideveleopment.info
Script:

REM +-----------------------------------------------------------------+
REM | FILE    : user_transaction_information.sql                      |
REM | AUTHOR  : Jeff Hunter                                           |
REM | PURPOSE : List TABLE locking AND user transactions information. | 
REM +-----------------------------------------------------------------+
 
SET LINESIZE 145
SET PAGESIZE 66
 
COLUMN owner        FORMAT a5      HEADING 'Owner'
COLUMN object_type  FORMAT a10     HEADING 'Type'
COLUMN object_name  FORMAT a25     HEADING 'Name'
COLUMN locked_mode  FORMAT a9      HEADING 'Locked Mode'
COLUMN sid          FORMAT 999     HEADING 'SID'
COLUMN username     FORMAT a15     HEADING 'Database User'
COLUMN osuser       FORMAT a10     HEADING 'O/S User'
COLUMN logon_time                  HEADING 'Login Time'
 
Prompt +----------------------------------------------------+
Prompt | TABLE Locking Information                          |
Prompt +----------------------------------------------------+
 
SELECT
    SUBSTR(b.owner, 1, 8)           owner
  , b.object_type                   object_type
  , SUBSTR(b.object_name, 1, 18)    object_name
  , DECODE(a.locked_mode
             , 0, 'None'
             , 1, 'Null'
             , 2, 'Row-S'
             , 3, 'Row-X'
             , 4, 'Share'
             , 5, 'S/Row-X'
             , 6, 'Exclusive')      locked_mode
  , a.session_id                    sid
  ,a.oracle_username                username
  , a.os_user_name                  osuser
  , TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time
FROM
    v$locked_object a
  , dba_objects b
  , v$session c
WHERE
      a.object_id  = b.object_id
  AND a.session_id = c.sid
ORDER BY
    b.owner
  , b.object_type
  , b.object_name
/
 
Prompt +----------------------------------------------------+
Prompt | User Transactions Information                      |
Prompt +----------------------------------------------------+
 
COLUMN "UserName" format a8
COLUMN "DB Sid" format 999999
COLUMN "Unix Pid" format 99999999
COLUMN "Trnx_start_time" format a19
COLUMN "Current Time" format a19
COLUMN "Elapsed(mins)" format 999999999.99
COLUMN "Undo Name" format a09
COLUMN "Used Undo Blks" format a13
COLUMN "Used Undo Size(Kb)" format a17
COLUMN "Logical I/O(Blks)" format 99999999999999999
COLUMN "Logical I/O(Kb)" format 999999999999999
COLUMN "Physical I/O(Blks)" format 999999999999999999
COLUMN "Physical I/O(Kb)" format 999999999999999999
 
SELECT
    a.username  "UserName"
  , a.sid       "DB Sid"
  , e.spid      "Unix Pid"
  , TO_CHAR(TO_DATE(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time"
  , TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time"
  , ROUND(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)"
  , c.segment_name "Undo Name"
  , TO_CHAR(b.used_ublk*d.value/1024) "Used Undo Size(Kb)"
  , TO_CHAR(b.used_ublk) "Used Undo Blks"
  , b.log_io "Logical I/O(Blks)"
  , b.log_io*d.value/1024 "Logical I/O(Kb)"
  , b.phy_io "Physical I/O(Blks)"
  , b.phy_io*d.value/1024 "Physical I/O(Kb)"
  , a.program 
FROM
    v$session         a
  , v$transaction     b
  , dba_rollback_segs c
  , v$parameter       d
  , v$process         e
WHERE
      b.ses_addr = a.saddr
  AND b.xidusn   = c.segment_id
  AND d.name     = 'db_block_size'
  AND e.ADDR     = a.PADDR
ORDER BY 4
/

Note:
In addition to an explicit LOCK SQL command run on the table (as Jonathan pointed out), another possibility of having a lock without a transaction is a DML with a long-running query as its prerequisite, such as
INSERT INTO YOURTABLE SELECT COLUMNS FROM SOMEBIGTABLE This immediately creates a TM lock in mode 3 on YOURTABLE, but won’t create a transaction until the query phase is finished.
When you have this problem, check v$sql.sql_text for the locking session and also its entry in v$session_wait.

Popularity: 20% [?]