ORA-04021: 等待锁定对象时发生超时

2021-12-24 00:00:00 oracle plsql sqlplus

我有这个匿名 PL/SQL 块，它计算并打印从表中返回的值.

I have this anonymous PL/SQL block which calculates and prints a value return from a table.

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;

这个块可以正常工作，但我想使用 PL/SQL 函数来做同样的事情

This block work correctly, but I wanted to do the same thing using a PL/SQL Function

我写的函数如下:

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;
/

当我尝试执行上面的代码将函数保存到数据库时，环境(SQL*PLUS)挂了很长时间，最后返回这个错误:

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:

错误在第 1 行:
ORA-04021: 等待锁定对象时发生超时

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

有什么问题???请！

推荐答案

听起来像ddl_lock问题

看看
dba_ddl_locks 以查看谁在阻止"创建或替换.

Take a look at
dba_ddl_locks to see who is "blocking" a create or replace.

还尝试在不同名称下创建 - 看看会发生什么.

Also try to create under different name - and see what happens.

相关文章