SQL statement used to check whether a lock table exists in Oracle

1. Check whether SQL statements with lock tables exist.
Copy codeThe Code is as follows:
Select 'blocker ('| lb. sid |': '| sb. username |')-SQL: '| qb. SQL _text blockers,
'Waiter ('| lw. sid |': '| sw. username |')-SQL: '| qw. SQL _text waiters
From v $ lock lb,
V $ lock lw,
V $ session sb,
V $ session sw,
V $ SQL qb,
V $ SQL qw
Where lb. sid = sb. sid
And lw. sid = sw. sid
And sb. prev_ SQL _addr = qb. address
And sw. SQL _address = qw. address
And lb. id1 = lw. id1
And sw. lockwait is not null
And sb. lockwait is null
And lb. block = 1;

2. view the locked table
Copy codeThe Code is as follows:
Select p. spid, a. serial #, c. object_name, B. session_id, B. oracle_username, B. OS _user_name
From v $ process p, v $ session a, v $ locked_object B, all_objects c
Where p. addr = a. paddr and a. process = B. process and c. object_id = B. object_id;

3. Check the user's process to cause deadlocks and lock levels.
Copy codeThe Code is as follows:
Select B. owner, B. object_name, l. session_id, l. locked_mode fromv $ locked_object l, dba_objects

4. view the connected Process
Copy codeThe Code is as follows:
SELECT sid, serial #, username, osuser FROMv $ session;

5. Check which session is caused
Copy codeThe Code is as follows:
Select B. username, B. sid, B. serial #, logon_time
From v $ locked_object a, v $ session B
Where a. session_id = B. sid order by B. logon_time;

6. Kill the process
Copy codeThe Code is as follows:
Alter system kill session 'sid, serial #';

Sid is the sid and serid obtained in step 1.