Shulou(Shulou.com)06/01 Report--

The check_lock.sql script lists the object lock (lock) information and its killed session (sid) Serial#) Information set linesize 132pagesize 66 break on Kill on username on terminal column Kill heading 'Kill String' format a13 column res heading' Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading' Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a35 heading "Table Name" column owner format a9 column Address format a18 column ctime heading "Seconds" select nvl (S.USERNAMEMagnum Internal') username, nvl (S.TERMINALLING) terminal L.SID | |','| | S.SERIAL# Kill, U1.NAME | |'. | | substr (T1.NAMEPower1, tab 20) tab, decode (L.LMODEPhone 1, "No Lock', 2", "Row Share', 3", "Row Exclusive', 4,"share", "share", 5 "share Row Exclusive',", "Exclusive6") lmode. Decode request, l.ctimefrom V$LOCK L, V$SESSION S, SYS.USER$ U1, request, l.ctimefrom V$LOCK L, V$SESSION S, SYS.USER$ U1 SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode) and U1.USER# = T1.OWNER# and S.TYPE! = 'BACKGROUND' order by 1 Magazine 2 BACKGROUND' order by 5 / session 1: create a table T1 [root @ Oel_10G ~] # su-oracle @ Oel_10G ~] $sqlplus scott/oracleSQL > create table T1 (I int) Table created.SQL > insert into T1 values (1), 1 row created.SQL > commit;Commit complete. Add a lock to T1 in session 1 SQL > select * from T1 for update; I-1 session 2: update a row of the T1 table in session 2. Because there is a Row Exclusive lock in the T1 table, session 2 cannot acquire a lock temporarily in the pending state [root@Oel_10G ~] # su-oracle @ Oel_10G ~] $sqlplus scott/oracleSQL > update T1 set item3 where item1 Session 3: update a row of the T1 table in session 3. Because there is a Row Exclusive lock in the T1 table, session 3 cannot acquire the lock temporarily in a pending state [root@Oel_10G ~] # su-oracle @ Oel_10G ~] $sqlplus scott/oracleSQL > update T1 set item5 where item1 Session 4: open a new session and log in as sys Execute lock_check.sql to view related object lock information [root@Oel_10G ~] # su-Oracle [Oracle @ Oel_10G ~] $sqlplus / as sysdbaSQL > @ check_lock.sqlUsername Term Kill String Table Name Lock Held Lock Requested Seconds-- -SCOTT pts/1 525891 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 108 SCOTT.T1 Row Exclusive 108SCOTT pts/2 544,8727 SCOTT.T1 Row Exclusive 72 PUBLIC.V$SQLAREA_PLAN_ HASH Exclusive 72SCOTT pts/3 522,5542 SCOTT.T1 Row Exclusive 6 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 66 rows selected. After executing the script, the result lists the related objects and lock information in table T1. Session 1 holds the row exclusive lock of T1, and both session 2 and session 3 are requesting Exclusive locks. Now we kill the session SQL > alter system kill session '522 set 5542 under scott users whose sid,serial# is (522). In session 3, we can see (sid,serial#) (522) (522) being killed, SQL > update T1, where iTunes 5 where ilocks. Update T1 set iTunes 5 where iTunes 1 * ERROR at line 1:ORA-00028: your session has been killed continues to execute check_lock.sqlSQL > @ check_lock.sqlUsername Term Kill String Table Name Lock Held Lock Requested Seconds--in session 4 -SCOTT pts/1 525891 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 388 SCOTT.T1 Row Exclusive 388SCOTT pts/2 544,8727 SCOTT.T1 Row Exclusive 352 PUBLIC.V$SQLAREA_PLAN_HASH Exclusive 352at this time Session 3 has been *

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.