EXECUTE IMMEDIATE执行DROP FUNCTION导致Oracle SQL Developer挂起问题

这是Oracle的对象依赖锁机制导致的正常现象，不是PL/SQL的特殊bug：

• 你编写的匿名PL/SQL块在运行时，会对它直接引用的所有依赖对象（也就是你提前创建的table_exists函数）加共享对象锁，该锁会一直持有到整个匿名块完全执行结束（包括正常结束和异常结束）
• 当块内执行EXECUTE IMMEDIATE 'DROP FUNCTION table_exists'时，DROP操作需要申请该函数的排他锁才能完成删除，但此时排他锁被当前运行的匿名块自己持有，就会出现无限等待的死锁场景，也就是你看到的SQL Developer挂起。
• 单独执行DROP FUNCTION table_exists;可以成功，是因为此时没有正在运行的程序持有该函数的共享锁，DROP可以正常拿到排他锁完成操作。
• 取消任务后函数没有被删除，是因为取消操作中断了还在等待锁的DROP语句，该语句未实际执行成功。

方案1：把删除逻辑移到匿名块外 #

直接将DROP语句放在匿名块结束符/之后，此时匿名块已经运行结束，会释放函数的共享锁，DROP可以正常执行：

-- 原有创建函数的逻辑不变
CREATE OR REPLACE FUNCTION table_exists(table_name VARCHAR2)
RETURN BOOLEAN
AS
    table_count NUMBER := 0;
    exists_sql VARCHAR2(255);
BEGIN
    exists_sql := 'SELECT COUNT(1) FROM tab WHERE tname = :tab_name';
    EXECUTE IMMEDIATE exists_sql INTO table_count USING table_name;

    RETURN table_count > 0;
END;
/

-- 原有匿名块去掉内部的DROP语句
DECLARE
    TYPE tables_array IS VARRAY(2) OF VARCHAR2(25); -- change varray size if running on dev
    tables tables_array;
BEGIN
    tables := tables_array(
        'FOO',
        'BAR'
    );

    FOR table_element IN 1 .. tables.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Backing up data for ' || tables(table_element));
        IF table_exists(tables(table_element) || '_ORIGINAL') THEN
            DBMS_OUTPUT.PUT_LINE(tables(table_element) || '_ORIGINAL already exists');
        ELSE
            EXECUTE IMMEDIATE 'CREATE TABLE ' || tables(table_element) || '_ORIGINAL AS SELECT * FROM '|| tables(table_element);
        END IF;
    END LOOP;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('Unexpected error: ' || sqlerrm);
    ROLLBACK;
    RAISE;
END;
/

-- 匿名块执行结束后再删除函数
DROP FUNCTION table_exists;
/

方案2：将函数改为匿名块的局部子程序 #

直接把table_exists函数定义放在匿名块的DECLARE部分，作为局部子程序，不会生成持久化的数据库对象，自然不需要后续删除，更适合临时逻辑的场景：

DECLARE
    -- 函数作为局部子程序定义在匿名块内部
    FUNCTION table_exists(table_name VARCHAR2)
    RETURN BOOLEAN
    AS
        table_count NUMBER := 0;
        exists_sql VARCHAR2(255);
    BEGIN
        exists_sql := 'SELECT COUNT(1) FROM tab WHERE tname = :tab_name';
        EXECUTE IMMEDIATE exists_sql INTO table_count USING table_name;
        RETURN table_count > 0;
    END;

    TYPE tables_array IS VARRAY(2) OF VARCHAR2(25);
    tables tables_array;
BEGIN
    tables := tables_array(
        'FOO',
        'BAR'
    );

    FOR table_element IN 1 .. tables.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Backing up data for ' || tables(table_element));
        IF table_exists(tables(table_element) || '_ORIGINAL') THEN
            DBMS_OUTPUT.PUT_LINE(tables(table_element) || '_ORIGINAL already exists');
        ELSE
            EXECUTE IMMEDIATE 'CREATE TABLE ' || tables(table_element) || '_ORIGINAL AS SELECT * FROM '|| tables(table_element);
        END IF;
    END LOOP;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('Unexpected error: ' || sqlerrm);
    ROLLBACK;
    RAISE;
END;
/

内容的提问来源于stack exchange，提问作者Peadar Ó Duinnín