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;