一次性編譯ORACLE中所有對象

這個預存程序不是我寫的，但是很好使，特別適合剛匯入對象看到滿螢幕的叉。

CREATE OR REPLACE  PROCEDURE "USER"."RECOMPILE_SCHEMA"  IS
    v_type user_objects.object_type%TYPE;
    v_name user_objects.object_name%TYPE;
    v_stat user_objects.status%TYPE;

    CURSOR c_obj IS
        SELECT base
          FROM (SELECT a.object_id base,
                       b.object_id rel
                  FROM user_objects    a,
                       user_objects    b,
                       sys.dependency$ c
                 WHERE a.object_id = c.d_obj# AND
                       b.object_id = c.p_obj# AND
                       a.object_type IN
                       ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY',
                       --  'VIEW',
                        'TRIGGER') AND
                       b.object_type IN
                       ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY',
                       --  'VIEW',
                        'TRIGGER') AND
                       NOT a.object_name = b.object_name) objects
        CONNECT BY base = PRIOR rel
         GROUP BY base
         ORDER BY MAX(LEVEL) DESC;
BEGIN
    -- loop through all objects in order of dependancy.
    FOR c_row IN c_obj LOOP
        -- select the objects attributes (type, name & status).
        SELECT object_type,
               object_name,
               status
          INTO v_type,
               v_name,
               v_stat
          FROM user_objects
         WHERE object_id = c_row.base;

        -- if the OBJECT is INVALID, recompile it.
        IF v_stat = 'INVALID' THEN
            dbms_ddl.alter_compile(v_type, USER, v_name);
        END IF;
    END LOOP;

    -- Recompile all remaining INVALID OBJECTS (all those without dependencies).
    FOR c_row IN (SELECT object_type,
                         object_name
                    FROM user_objects
                   WHERE status = 'INVALID' AND
                         object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION',
                          'TRIGGER', 'PACKAGE BODY',
                         --   'VIEW',
                          'TRIGGER')) LOOP
        dbms_ddl.alter_compile(c_row.object_type, USER, c_row.object_name);
    END LOOP;
END recompile_schema;

要編譯一個使用者的對象，只要用該使用者的口令登陸，而後執行預存程序即可。
執行前還需把SYS使用者的dependency$ 給授權。