oracle ORA-04021: 等待锁定对象时发生超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39051788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-04021: timeout occurred while waiting to lock object
提问by Saddam Meshaal
I have this anonymous PL/SQL block which calculates and prints a value return from a table.
我有这个匿名 PL/SQL 块，它计算并打印从表中返回的值。
DECLARE
U_ID NUMBER :=39;
RETAIL BINARY_FLOAT:=1;
FLAG NUMBER;
BEGIN
SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID INTO RETAIL, FLAG FROM UNITS WHERE UNIT_ID=U_ID;
LOOP
SELECT NVL(MAX(UNIT_ID),U_ID) INTO FLAG FROM UNITS WHERE FATHER_ID=FLAG;
IF FLAG=U_ID THEN EXIT; END IF;
SELECT RETAIL* RETAIL_AMOUNT INTO RETAIL FROM UNITS WHERE UNIT_ID=FLAG;
EXIT WHEN FLAG=U_ID;
END LOOP;
DBMS_OUTPUT.PUT_LINE( RETAIL);
END;
This block work correctly, but I wanted to do the same thing using a PL/SQL Function
这个块工作正常，但我想使用 PL/SQL 函数做同样的事情
I wrote the function as follow:
我写的函数如下：
CREATE OR REPLACE FUNCTION GET_UNIT_RETAIL(U_ID NUMBER)
RETURN NUMBER
IS
RETAIL BINARY_FLOAT:=1;
FLAG NUMBER;
BEGIN
SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID
INTO RETAIL, FLAG
FROM UNITS
WHERE UNIT_ID=U_ID;
LOOP
SELECT NVL(MAX(UNIT_ID),U_ID)
INTO FLAG
FROM UNITS
WHERE FATHER_ID=FLAG;
IF FLAG=U_ID THEN
EXIT;
END IF;
SELECT RETAIL* RETAIL_AMOUNT
INTO RETAIL
FROM UNITS
WHERE UNIT_ID=FLAG;
EXIT WHEN FLAG=U_ID;
END LOOP;
RETURN NUMBER;
END;
/
When I try to execute the above code to save the function to the database, the environment (SQL*PLUS) hangs for a long time and at the end returns this error:
当我尝试执行上面的代码将函数保存到数据库时，环境（SQL*PLUS）挂了很长时间，最后返回这个错误：
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
第 1 行的错误：
ORA-04021：等待锁定对象时发生超时
What is the problem ??? Please !
问题是什么 ？？？请 ！
回答by Plirkee
Sounds like ddl_lockproblem
听起来像ddl_lock问题
Take a look atdba_ddl_locksto see who is "blocking" a create or replace.
看看dba_ddl_locks，看看谁是“堵”一个创建或替换。
Also try to create under different name- and see what happens.
还尝试以不同的名称创建- 看看会发生什么。
回答by Saddam Meshaal
The problem was because the Object GET_UNIT_RETAILwas busy by other environment
Here is the answer:
问题是因为对象GET_UNIT_RETAIL被其他环境忙了这里是答案：
