ORA-04021 timeout occurred while waiting to lock object
用户要求删除一个数据库的用户 GREENPASS，在删除的过程中，报错如下：
drop user GREENPASS * ERROR at line 1: ORA-04021: timeout occurred while waiting to lock object NEWDR.HNDR_SMS_LOG
处理过程
1. 首先查询NEWDR.HNDR_SMS_LOG，这是一个'PROCEDURE'；
2. 现在报错，不能对此对象进行锁定。
3. 看看什么会话在访问此对象：
select B.SID, b.USERNAME, b.MACHINE FROM V$ACCESS A, V$SESSION B WHERE A.SID = B.SID and a.OBJECT = 'HNDR_SMS_LOG' and a.TYPE = 'PROCEDURE';
SID USERNAME MACHINE ---------- ------------ ------------------- 520 NEWDR WORKGROUP\BONC02
4. 根据sid获取操作系统的进程号
select spid os_sid from v$process where addr = (select paddr from v$session where sid = &oracle_sid);
old 3: where addr = (select paddr from v$session where sid = &oracle_sid) new 3: where addr = (select paddr from v$session where sid = 520)
OS_SID ------------ 29360
5. 询问业务人员，可以直接杀掉进程
ps -ef|grep 29360 kill -9 29360
6. 重新尝试 SQL> drop user GREENPASS; 用户已丢弃
-----------------------------------------------
sid 为 520 对应的语句 部分如下：
INSERT /*+ APPEND */ INTO FACT_INFO_USER_BEFORE (ACCT_MONTH, USER_NO, DEVICE_NUMBER, SERVICE_CODE, AREA_ID, CITY_ID, USER_DINNER, INNET_MONTH, AVG_ARPU, ACCT_ARPU, IS_ACCT, USER_TYPE, EXPIRE_MONTH) SELECT /*+ USE_HASH(A,B,C,D,E,F,G)*/ :B1, C.USER_NO, C.DEVICE_NUMBER, C.TELE_TYPE, C.AREA_NO, C.CITY_NO, C.USER_DINNER, C.INNET_MONTH, A.AVG_FEE, B.ALL_FEE, NVL2(B.USER_NO, '1', '0'), (CASE WHEN F.CLUBMEM_LEVEL_SUB_CODE_LAST IN ('1', '2') AND F.CLUBMEM_LEVEL_SUB_CODE = '1' AND G.USER_NO IS NULL THEN '1' WHEN F.CLUBMEM_LEVEL_SUB_CODE_LAST IN ('1', '2') AND F.CLUBMEM_LEVEL_SUB_CODE = '2' AND G.USER_NO IS NULL THEN '2' WHEN F.CLUBMEM_LEVEL_SUB_CODE = '3' AND C.INNET_MONTH >= 4 AND A.AVG_FEE >= D.HIGH_STANDARD AND G.USER_NO IS NULL THEN '3' WHEN C.INNET_MONTH >= 4 AND A.AVG_FEE >= D.HIGH_STANDARD AND G.USER_NO IS NULL THEN '4' WHEN C.INNET_MONTH >= 4 AND A.AVG_FEE < D.HIGH_STANDARD AND A.AVG_FEE >= D.HIGH_STANDARD * 0.8 AND E.USER_TYPE <= '5' AND G.USER_NO IS NULL THEN '5' WHEN C.INNET_MONTH >= 4 AND A.AVG_FEE < D.HIGH_STANDARD AND A.AVG_FEE >= D.HIGH_STANDARD * 0.8 AND E.USER_TYPE > '5' THEN '6' WHEN C.INNET_MONTH >= 4 AND A.AVG_FEE < D.HIGH_STANDARD * 0.8 AND A.AVG_FEE >= 30 THEN '6'
