blog menu1

Locked_Objects


Locked_Objects






Lock Demo
Locking Demo
Session 1		Session 2

conn / as sysdbaconn scott/tiger

UPDATE emp
SET deptno=deptno+10
WHERE deptno < 40;
SELECT username, gv$lock.sid,
TRUNC(id1/power(2,16)) rbs,
BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq, lmode, request
FROM gv$lock, gv$session
WHERE gv$lock.type = 'TX'
AND gv$lock.sid = gv$session.sid
AND gv$session.username = 'SCOTT';
SELECT XIDUSN, XIDSLOT, XIDSQN
FROM gv$transaction;

rollback;

Lock Table
Table LockingLOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT | WAIT <seconds>];
LOCK TABLE uwclass.servers IN exclusive MODE WAIT 120;

Lock Related Queries
Active Table LocksSELECT SUBSTR(a.object,1,25) TABLENAME,
SUBSTR(s.username,1,15) USERNAME,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.type,
'RT','Redo Log Buffer',
'TD','Dictionary',
'TM','DML',
'TS','Temp Segments',
'TX','Transaction',
'UL','User',
'RW','Row Wait',
l.type) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.username, p.pid, l.type, p.spid
ORDER BY a.object, s.username;
Active LocksSELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name
object, k.kaddr, DECODE(l.locked_mode,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Shared Table',
5, 'Shared Row Exclusive',
6, 'Exclusive') locked_mode,
DECODE(k.type,
'BL','Buffer Cache Management (PCM lock)',
'CF','Controlfile Transaction',
'CI','Cross Instance Call',
'CU','Bind Enqueue',
'DF','Data File',
'DL','Direct Loader',
'DM','Database Mount',
'DR','Distributed Recovery',
'DX','Distributed Transaction',
'FS','File Set',
'IN','Instance Number',
'IR','Instance Recovery',
'IS','Instance State',
'IV','Library Cache Invalidation',
'JQ','Job Queue',
'KK','Redo Log Kick',
'LA','Library Cache Lock',
'LB','Library Cache Lock',
'LC','Library Cache Lock',
'LD','Library Cache Lock',
'LE','Library Cache Lock',
'LF','Library Cache Lock',
'LG','Library Cache Lock',
'LH','Library Cache Lock',
'LI','Library Cache Lock',
'LJ','Library Cache Lock',
'LK','Library Cache Lock',
'LL','Library Cache Lock',
'LM','Library Cache Lock',
'LN','Library Cache Lock',
'LO','Library Cache Lock',
'LP','Library Cache Lock',
'MM','Mount Definition',
'MR','Media Recovery',
'NA','Library Cache Pin',
'NB','Library Cache Pin',
'NC','Library Cache Pin',
'ND','Library Cache Pin',
'NE','Library Cache Pin',
'NF','Library Cache Pin',
'NG','Library Cache Pin',
'NH','Library Cache Pin',
'NI','Library Cache Pin',
'NJ','Library Cache Pin',
'NK','Library Cache Pin',
'NL','Library Cache Pin',
'NM','Library Cache Pin',
'NN','Library Cache Pin',
'NO','Library Cache Pin',
'NP','Library Cache Pin',
'NQ','Library Cache Pin',
'NR','Library Cache Pin',
'NS','Library Cache Pin',
'NT','Library Cache Pin',
'NU','Library Cache Pin',
'NV','Library Cache Pin',
'NW','Library Cache Pin',
'NX','Library Cache Pin',
'NY','Library Cache Pin',
'NZ','Library Cache Pin',
'PF','Password File',
'PI','Parallel Slaves',
'PR','Process Startup',
'PS','Parallel Slave Synchronization',
'QA','Row Cache Lock',
'QB','Row Cache Lock',
'QC','Row Cache Lock',
'QD','Row Cache Lock',
'QE','Row Cache Lock',
'QF','Row Cache Lock',
'QG','Row Cache Lock',
'QH','Row Cache Lock',
'QI','Row Cache Lock',
'QJ','Row Cache Lock',
'QK','Row Cache Lock',
'QL','Row Cache Lock',
'QM','Row Cache Lock',
'QN','Row Cache Lock',
'QO','Row Cache Lock',
'QP','Row Cache Lock',
'QQ','Row Cache Lock',
'QR','Row Cache Lock',
'QS','Row Cache Lock',
'QT','Row Cache Lock',
'QU','Row Cache Lock',
'QV','Row Cache Lock',
'QW','Row Cache Lock',
'QX','Row Cache Lock',
'QY','Row Cache Lock',
'QZ','Row Cache Lock',
'RT','Redo Thread',
'SC','System Commit number',
'SM','SMON synchronization',
'SN','Sequence Number',
'SQ','Sequence Enqueue',
'SR','Synchronous Replication',
'SS','Sort Segment',
'ST','Space Management Transaction',
'SV','Sequence Number Value',
'TA','Transaction Recovery',
'TM','DML Enqueue',
'TS','Table Space (or Temporary Segment)',
'TT','Temporary Table',
'TX','Transaction',
'UL','User-defined Locks',
'UN','User Name',
'US','Undo segment Serialization',
'WL','Writing redo Log',
'XA','Instance Attribute Lock',
'XI','Instance Registration Lock') type
FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l,
dba_objects o, sys.gv$lock k, gv$lock v
WHERE o.object_id = l.object_id
AND l.session_id = s.sid
AND k.sid = s.sid
AND s.saddr = c.saddr
AND k.kaddr = c.kaddr
AND k.kaddr = v.kaddr
AND v.saddr = s.saddr
AND k.lmode = l.locked_mode
AND k.lmode = c.lmode
AND k.request = c.request
ORDER BY object;
List Locksset wrap off
col lock_type format a12
col mode_held format a10
col mode_requested format a10
col blocking_others format a20
col username format a10

SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');
Locked ObjectsSELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;
Locked ObjectsSELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Locked ObjectsSELECT DECODE(request,0,'Holder: ','Waiter:')
sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;
Objects that have been lock for 2 minutes or more SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 120
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert desc;

Killing Oracle Sessions

There are a number of ways to kill rogue sessions both within Oracle and externally.

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.
  • 
SET LINESIZE 100
    

    
COLUMN spid FORMAT A10
    

    
COLUMN username FORMAT A10
    
COLUMN program FORMAT A45

    
       s.sid,
    
SELECT s.inst_id,
       s.serial#,

    
       s.program
    
p.spid,
       s.username,

    
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
    
FROM   gv$session s
WHERE  s.type != 'BACKGROUND';

    
   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
    
---------- ---------- ---------- ---------- ---------- ---------------------------------------------

    
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
    
1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)

    
SQL>
    

    
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.
  • SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
  • SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
  • SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to theKILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
  • 
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
    

    
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
  • 
SQL> alter system disconnect session '30,7';
    
alter system disconnect session '30,7'

    
ERROR at line 1:
    
*

    
SQL>
    

    
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
  • C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
  • % kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
  • % kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
  • % ps -ef | grep ora
The session thread should be killed immediately and all resources released.

No comments:

Post a Comment

Subscribe to: Posts (Atom)
  • Redshift - dataypes
    Amazon Redshift Data Types When you are setting up a connection to an external data source, Spotfire needs to map the data types in the...
  • Oracle DB Growth
    SET   SERVEROUT   ON   SET   VERIFY   OFF   SET   LINES 200   SET   PAGES 2000   DECLARE      v_ts_id                   ...
  • Kafka - Overview
    Producer  --->>  Kakfa server (Kafka Broker), cluster is a group of broker which has  a toic - ---> consumer (request back for mo...