Query the locked table in Oracle and release the session

Query SQL:

Select a. Owner,
A. object_name,
B. xidusn,
B. xidslot,
B. xidsqn,
B. session_id,
B. oracle_username,
B. OS _user_name,
B. process,
B. locked_mode,
C. machine,
C. Status,
C. server,
C. Sid,
C. Serial #,
C. Program
From all_objects,
V $ locked_object B,
SYS. GV _ $ Session C
Where (A. object_id = B. object_id)
And (B. Process = C. Process)
Order by 1, 2

Release session SQL:

Alter system kill session 'sid, serial #'
Alter system kill session '2017 32'
Alter system kill session '2017 8'

 

 

Which of the following objects query the lock status?
V $ lock, V $ locked_object, V $ session, V $ sqlarea, V $ process;
To query a locked table:
Select S. sid session_id, S. username, decode (lmode, 0, 'none', 1, 'null', 2, 'row-s (SS) ', 3, 'row-X (SX )', 4, 'share', 5,'s/row-X (ssx) ', 6, 'clusive', to_char (lmode) mode_held, decode (request, 0, 'none', 1, 'null', 2, 'row-s (SS) ', 3, 'row-X (SX)', 4, 'share ', 5, 's/row-X (ssx) ', 6, 'clusive', to_char (request) mode_requested, O. owner | '. '| O. object_name | '(' | O. object_type | ')', S. type lock_type, L. id1 lock_id1, L. id2 lock_id2 from V $ lock l, sys. dba_objects o, V $ session s where L. SID = S. sid and L. id1 = O. object_id;

 

Check the deadlock script in Oracle

select substr (V $ lock. sid,) "Sid",
substr (username,) "username",
substr (object_name,) "objectname ",
V $ lock. type "locktype",
decode (rtrim (substr (lmode, 1, 4),
'2', 'row-s (SS) ', '3 ', 'row-X (SX) ',
'4', 'share', '5','s/row-X (ssx )',
'6', 'clusive ', 'other') "lockmode",
substr (V $ session. program, 25, 1) "programname"
from V $ lock, sys. dba_objects, V $ session
wh Ere (object_id = V $ lock. id1
and V $ lock. SID = V $ session. sid
and username is not null
and username not in ('sys ', 'system')
and serial #! = 1);

 

View lock table processSQLStatement 1:
Select sess. Sid,
Sess. Serial #,
Lo. oracle_username,
Lo. OS _user_name,
AO. object_name,
Lo. locked_mode
From v $ locked_object Lo,
Dba_objects AO,
V $ session sess
Where ao. object_id = Lo. object_id and Lo. session_id = sess. Sid;

View the lock table process SQL statement 2:
Select * from V $ session T1, V $ locked_object T2 where t1.sid = t2.session _ id;

Kill the lock table process:
If there is a record, it indicates that there is a lock. Remember the SID and serial # Under the record, and replace the ID of the record with the values and below to unlock the lock.
Alter system kill session '2017 9 ';