SQL> grant connect,resource to robin identified by robin; Grant succeeded.
SQL> grant connect,resource to alin identified by alin; Grant succeeded.
SQL> grant select,insert,update,all on test1 to robin,alin; Grant succeeded.
SQL> create table test1(name varchar2(30),id number); Table created.
SQL> insert into test1 values('a',1); 1 row created.
SQL> insert into test1 values('b',2); 1 row created.
SQL> commit; Commit complete.
SQL> select * from sys.test1; no rows selected
From Robin session: SQL> conn robin/robin@dba50 Connected.
SQL> select * from sys.test1; NAME ID ------------------------------ ---------- a 1 b 2
SQL> update sys.test1 set name='T' where id =1; 1 row updated. From Alin Session: SQL> conn alin/alin@dba50 Connected.
SQL> select * from sys.test1;
NAME ID ------------------------------ ---------- a 1 b 2
SQL> update sys.test1 set name='U' where id=1;
From SYS: SQL> select sid,block from v$lock;
SID BLOCK ---------- ---------- 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0
SID BLOCK ---------- ---------- 3 0 4 0 5 0 12 1 12 0 16 0 16 0 18 rows selected.
SQL> select username from v$session where sid=12; USERNAME ------------------------------ ROBIN
SQL> select waiting_session,holding_session from dba_waiters; WAITING_SESSION HOLDING_SESSION --------------- -------------------------------------------- 16 12
SQL> select username from v$session where sid=16; USERNAME ------------------------------ ALIN
SQL> select * from dba_blockers; HOLDING_SESSION --------------- 12 [NOTE]- dba_waiters and dba-blockers if not exists then run C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catblock.sql
SQL> select object_id from v$locked_object;
OBJECT_ID ---------- 30577 30577
SQL> select object_id,object_name from dba_objects where object_id=30577;
OBJECT_ID OBJECT_NAME ------------------------------------------------------------------------------------------------------------ 30577 TEST1
SQL> select sid,serial# from v$session where username='ROBIN';
SID SERIAL# ---------- ---------- 12 60
SQL> select sid,serial# from v$session where sid=12;
SID SERIAL# ---------- ---------- 12 60
SQL> alter system kill session '12,60';
System altered.
After that From Robbin session loc will release and alin session hang resolve.
SQL> select * from test1;
NAME ID ------------------------------ ---------- a 1 b 2
From Robin Session: SQL> select * from sys.test1; select * from sys.test1 * ERROR at line 1: ORA-00028: your session has been killed
Above things can be done by running the below script from sys (but above procedure is better)
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utllockt.sql; |