Solution to Oracle locking

Some performance Alarms can be found in the dba_outstanding_alerts table. If the locked table cannot be released for a long time, the related session information can be found here.

Some performance Alarms can be found in the dba_outstanding_alerts table. If the locked table cannot be released for a long time, the related session information can be found here.

Some performance Alarms can be found through the dba_outstanding_alerts table. If the locked table cannot be released for a long time, the sid and serial # Of the relevant session can be found here #.

Select * from dba_outstanding_alerts

In the v $ locked_object view, you can see the currently locked objects. Only those existing sessions can be locked. Therefore, you cannot draw conclusions in a rush, especially in the RAC environment, you must check each node to find the relevant session.

Select * from v $ locked_object

Sometimes you can view the lock information based on the Object Name:

Select sid, id1, type from v $ lock where id1 = (select object_id from dba_objects where object_name = upper ('mytablename '));

Find the session that causes the lock, and find the corresponding sid and serial #

Select saddr, sid, serial #, paddr, username, status from v $ session where sid = 772

Thus, the session can be killed:

SQL> alter system kill session '1234564 ';

System altered.

You can query the associations between Oracle Database processes and operating system processes:

Select spid, oSUSEr, s. program from v $ session s, v $ process p
Where s. paddr = p. addr and s. sid = 772

Sometimes some processes need to be killed in the operating system, you can use the kill-9 pid command:

[Root @ erpdevdb ~] # Ps-ef | grep sqlplus
Oracle 11847 11126 0 00:00:00 pts/1 sqlplus-S @/oracle/home/droptable. SQL
Root 11889 11856 0 00:00:00 pts/2 grep sqlplus
[Root @ erpdevdb ~] # Kill-9 11847

Blocking caused by database object locking is troublesome, so be careful when dealing with it.