Loc Resolve

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;

Comments