[Oracle] locked object 조회하기 (9i)

 

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 Object       FORMAT a27     HEADING 'Object'
COLUMN locked_mode  FORMAT a20     HEADING 'Locked Mode'
COLUMN sid          FORMAT 999999  HEADING 'DB SID'
COLUMN username     FORMAT a15     HEADING 'Locker'
COLUMN osuser       FORMAT a10     HEADING 'O/S User'
COLUMN logon_time                  HEADING 'Login Time'

prompt
Prompt +-----------------------------------------------------------------+
Prompt | Table Locking Info FROM v$locked_object, dba_objects, v$session |
Prompt +-----------------------------------------------------------------+

SELECT
      SUBSTR(b.owner, 1, 8)||'.'||SUBSTR(b.object_name, 1, 18) "Object"
  , b.object_type                   object_type
  , DECODE(a.locked_mode
             , 0, 'NONE'
             , 1, 'NULL'
             , 2, 'ROW SHARE'
             , 3, 'ROW EXCLUSIVE'
             , 4, 'SHARE'
             , 5, 'SHARE ROW EXCLUSIVE'
             , 6, 'EXCLUSIVE')      locked_mode
  , a.session_id                    "DB Sid"
  ,a.oracle_username                "Locker"
  , 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
/

COLUMN UserName     FORMAT a20      HEADING 'UserName'
COLUMN "DB Sid" FORMAT 999999      HEADING 'DB Sid'
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

Prompt +-------------------------------------------------------------------------------------------------+
Prompt | User Transactions Info FROM v$session, v$transaction, dba_rollback_segs, v$parameter, v$process |
Prompt +-------------------------------------------------------------------------------------------------+

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
/

Prompt +-------------------------------------------------------------------------------+
Prompt | Objects owned by users being accessed FROM v$session, v$transaction, v$Access |
Prompt +-------------------------------------------------------------------------------+

COLUMN TYPE         FORMAT a24     HEADING 'Type'
COLUMN object       FORMAT a30     HEADING 'Object'
COLUMN UserName     FORMAT a20      HEADING 'UserName'
COLUMN "DB Sid"     FORMAT 999999

SELECT
    a.username  "UserName"
  , a.sid       "DB Sid"
  , SUBSTR(c.owner, 1, 8)||'.'||SUBSTR(c.object, 1, 18) "Object"
  , c.type      "Type"
FROM v$session        a
   , v$transaction    b
   , v$Access         c
WHERE
     a.saddr     = b.ses_addr
AND  c.sid       = a.sid
AND  c.owner NOT IN ('SYS')
ORDER BY a.sid, c.type
/

Prompt +-------------------------------------------+
Prompt |  Blocked objects from V$LOCK and SYS.OBJ$ |
Prompt +-------------------------------------------+

COLUMN BLOCKED_OBJ FORMAT a35 HEADING 'Blocked Object'

SELECT /*+ ORDERED */
    l.sid
,   l.lmode
,   TRUNC(l.ctime/60) min_blocked
,   u.name||'.'||o.NAME blocked_obj
FROM (SELECT *
      FROM v$lock
      WHERE type='TM'
      AND sid IN (SELECT sid
                  FROM v$lock
                  WHERE block !=0 )) l
,     sys.obj$ o
,     sys.user$ u
WHERE o.obj# = l.ID1
AND   o.OWNER# = u.user#
/

Prompt +-------------------------------+
Prompt |  Blocked sessions from V$LOCK |
Prompt +-------------------------------+

SELECT /*+ ORDERED */
   blocker.sid blocker_sid
,  blocked.sid blocked_sid
,  TRUNC(blocked.ctime/60) min_blocked
,  blocked.request
FROM (SELECT *
      FROM v$lock
      WHERE block != 0
      AND type = 'TX') blocker
,    v$lock        blocked
WHERE blocked.type='TX'
AND blocked.block = 0
AND blocked.id1 = blocker.id1
/

CLEAR COLUMN
CLEAR BREAKS
SET LINES 80
SET TRIMS on
SET PAGES 9000
COLUMN sql_text FORMAT a70 word_wrapped
COLUMN sid FORMAT a10 NOPRINT new_value n_sid
COLUMN serial FORMAT a10 NOPRINT new_value n_serial
COLUMN username FORMAT a20 NOPRINT new_value n_username
COLUMN machine FORMAT a20 NOPRINT new_value n_machine
COLUMN osuser FORMAT a20 NOPRINT new_value n_osuser
COLUMN process FORMAT a20 NOPRINT new_value n_process
COLUMN action FORMAT a45 NOPRINT new_value n_action

Prompt +--------------------------------------------------------+
Prompt |  Blockers session details from V$SESSION and V$SQLTEXT |
Prompt +--------------------------------------------------------+

BREAK ON sid ON serial ON username ON process ON machine ON actiON skip page

ttitle -
       "Sid .......... : "  n_sid -
      skip 1 -
       "Serial ....... : "  n_serial -
      skip 1 -
       "Username ..... : "  n_username -
      skip 1 -
       "Machine ...... : "  n_machine -
      skip 1 -
       "OSuser ....... : "  n_osuser -
      skip 1 -
       "Process ...... : "  n_process -
      skip 1 -
       "Action ....... : "  n_action -

SELECT /*+ ORDERED */
   sid,serial# serial,username,machine,osuser,process,module||' '||action action,sql_text
FROM v$session ses, v$sqltext txt
WHERE txt.address(+) = ses.sql_address
AND   txt.hash_value(+) = ses.sql_hash_value
AND   ses.sid IN (SELECT sid
      FROM v$lock
      WHERE block != 0
      AND type = 'TX')
ORDER BY ses.sid,txt.piece
/

 

출처 : http://www.tek-tips.com/viewthread.cfm?qid=1291771&page=9

 

 

 

 

출처 : http://npnglife.tistory.com/100

 

[oracle] 오라클 테이블 락 확인 및 해제 lock(9i) Database

Programming/DB 2008/08/04 11:49

오라클 테이블 락 확인 및 해제 lock(9i)
 
-- 테이블에 lock을 발생시킨 user를 찾을 경우가 많습니다. 
-- 특히 OPS(RAC on 9i) system에서는 더더욱 그런 경우가 많구요. 
-- 제가 관리하던 시스템에서 자주 썼던 sql입니다.
-- 기타 많은 유사한 sql로 있습니다만 한번 써 보시길 바랍니다.

/* 락발생 사용자와 sql, object 조회 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$locked_object x, v$session a, v$sqltext b, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
and a.sql_address = b.address
--and a.terminal = ''
order by b.address,b.piece;

/* 락 발생 사용자확인 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
--select  'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
from gv$locked_object x, gv$session a, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
order by logon_time;
;

/* 현재 접속자의 sql 분석 */

select distinct a.sid,a.serial#,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;

/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '51,1111; 
 
SQL> ed
file afiedt.buf(이)가 기록되었습니다
  1  select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
  2  --select  'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
  3  from gv$locked_object x, gv$session a, dba_objects d
  4  where x.session_id = a.sid
  5  and x.object_id = d.object_id
  6* order by logon_time
SQL> /
SESSION_ID    SERIAL# OBJECT_NAME                                                                                                              MACHINE
---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ---------
------
        11         35 MR_CMSAPPLYDT                                                                                                            MSHOME\JAEHYUK
        11         35 MR_CMSDT                                                                                                                 MSHOME\JAEHYUK
        12        280 MR_CMSDT                                                                                                                 MSHOME\JAEHYUK
        14         16 MR_CMSDT                                                                                                                 MSHOME\JAEHYUK

SQL> alter system kill session '11,35';
시스템이 변경되었습니다.