Quick lock search and lock wait in Oracle

Database locks are resource-consuming. In particular, when a lock wait occurs, we must find the lock waiting to kill the process if possible.

You can useAlter system kill session 'sid, serial #'OrAlter system disconnect session 'sid, serial # 'immediate; To kill a session

The following statement finds the locks generated by all the DML statements in the database. It can also be found that any DML statement actually produces two locks: one is the table lock and the other is the row lock.

select/* + rule */s. username,
decode (L. type, 'Tm ', 'tablelock', 'tx ', 'row lock', null) lock_level,
O. owner, O. object_name, O. object_type,
S. sid, S. serial #, S. terminal, S. machine, S. program, S. osuser
from V $ session S, V $ lockl, dba_objects o
where L. SID = S. sid
and L. id1 = O. object_id (+)
and S. username is not null

If a lock wait occurs, we may want to know who is waiting because the table is locked. The following statement can query who has locked the table and who is waiting.
Select/* + rule */lpad ('', decode (L. xidusn, 0, 3, 0) | L. oracle_username user_name,
O. Owner, O. object_name, O. object_type, S. Sid, S. Serial #
From v $ locked_objectl, dba_objects o, V $ session s
Where l. object_id = O. object_id
And l. session_id = S. Sid
Order by O. object_id, xidusndesc

The preceding query result is a tree structure. If a subnode exists, it indicates that a wait occurs. If you want to know which rollback segment the lock uses, you can also associate it with V $ rollname. xidusn is the USN of the rollback segment.

[Q] how to effectively delete a large table (with many extent tables)
[a] A table with many (100 k) extent tables, if you simply use drop table, it will consume a lot of CPU (Oracle needs to operate on the FET $ and uet $ data dictionary), and it may take several days, A better way is to delete extent multiple times to reduce this consumption:
1. truncate table big-Table reuse storage;
2. alter table big-tabledeallocate unused keep 2000 m (n-1/N of the original size);
3. alter table big-tabledeallocate unused keep 1500 m;
....
4. drop table big-table;