SQL statement used to list the tables with deadlocks in the oralcle Database

SQL statement used to list the tables of the deadlock in the oralcle database:

============================================
Select a. username, A. Sid, A. Serial #, B. id1, C. SQL _text
From v $ session A, V $ lock B, V $ sqltext C
Where exists
(Select 1
From v $ Session D, V $ lock E
Where D. lockwait = E. kaddr
And E. id1 = B. id1)
And a. Sid = B. Sid
And C. hash_value = A. SQL _hash_value
And B. Request = 0;
==============================================
Select Sys. V _ $ session. osuser, SYS. V _ $ session. Machine, V $ lock. Sid,
SYS. V _ $ session. Serial #,
Decode (V $ lock. type,
'Mr ', 'Media recovery ',
'Redo thread', 'redo thread ',
'Non', 'user name ',
'Tx ', 'Transaction ',
'Tm', 'dml ',
'Ul ', 'pl/SQL user lock ',
'Dx ', 'stributed xaction ',
'Cf ', 'control file ',
'Is ', 'instance State ',
'Fs', 'file set ',
'Ir', 'instance recovery ',
'St', 'disk space transaction ',
'Ts', 'temp segment ',
'Iv ', 'library cache invalida-tion ',
'Ls', 'Log start or switch ',
'Rw ', 'row wait ',
'Sq ', 'sequence number ',
'Te', 'extend table ',
'TT', 'temp table ',
'Unknown ') locktype,
Rtrim (object_type) | ''| rtrim (owner) | '.' | object_name as object_name,
Decode (lmode, 0, 'none ',
1, 'null ',
2, 'row-S ',
3, 'row-x ',
4, 'share ',
5,'s/row-X ',
6, 'clusive ', 'unknown') lockmode,
Decode (request, 0, 'none ',
1, 'null ',
2, 'row-S ',
3, 'row-x ',
4, 'share ',
5,'s/row-X ',
6, 'clusive ', 'unknown') requestmode,
Ctime, Block B
From v $ lock, all_objects, SYS. V _ $ session
Where V $ lock. Sid> 6
And SYS. V _ $ session. Sid = V $ lock. Sid
And v $ lock. id1 = all_objects.object_id;
========================================================== =

 

Determine whether a table has a locked SQL statement:

Select * from V $ locked_object where object_id = (select object_id from dba_objects
Where object_name = 'table _ name ')