Published: 4/10/2026

Recompile All Invalid User Objects

APEX Database PL/SQL

Oracle Database — PL/SQL Utility Script, uses ALL_OBJECTS to identify and recompile all invalid objects for the current user, in dependency-safe order.

Notes

Multi-Pass Wrapper (Optional)

Topic	Detail
Compile order	Types and synonyms first, then package specs before bodies, then standalone programs, views, and triggers last. This minimizes cascade failures.
COMPILE BODY syntax	`ALTER PACKAGE pkg COMPILE BODY` — not `ALTER PACKAGE BODY pkg COMPILE`. The script handles this translation.
Multi-pass	Circular or deep dependency chains may need 2–3 passes. The multi-pass wrapper exits when no further progress is made or after 5 attempts.
Privileges	Requires `ALTER ANY PROCEDURE`, `ALTER ANY TRIGGER`, `ALTER ANY TYPE` if recompiling objects outside your own schema.
Alternative	Oracle provides `UTL_RECOMP.RECOMP_SERIAL()` and `UTL_RECOMP.RECOMP_PARALLEL()` as built-in utilities, but these require SYS/SYSDBA and operate at the schema or database level.

Variant

To recompile objects across a specific schema (not just the current user), replace the WHERE filter:

-- Replace this:
AND owner = USER
-- With a specific schema:
AND owner = 'YOUR_SCHEMA_NAME'
-- Or exclude Oracle-maintained schemas for all user schemas:
AND owner NOT IN (
SELECT username FROM all_users WHERE oracle_maintained = 'Y'
)

Main Recompile Script

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
    v_sql    VARCHAR2(4000);
    v_count  PLS_INTEGER := 0;
    v_errors PLS_INTEGER := 0;
BEGIN
    dbms_output.put_line('=== Recompiling Invalid Objects for '
                         || user
                         || ' ===');
    dbms_output.put_line('Start: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
    dbms_output.put_line('---');
-- Loop through invalid objects in dependency-safe order
    FOR rec IN (
        SELECT
            owner,
            object_name,
            object_type,
            CASE object_type
                WHEN 'TYPE SPECIFICATION' THEN
                    'TYPE'
                WHEN 'TYPE BODY'          THEN
                    'TYPE BODY'
                WHEN 'PACKAGE'            THEN
                    'PACKAGE'
                WHEN 'PACKAGE BODY'       THEN
                    'PACKAGE BODY'
                WHEN 'FUNCTION'           THEN
                    'FUNCTION'
                WHEN 'PROCEDURE'          THEN
                    'PROCEDURE'
                WHEN 'VIEW'               THEN
                    'VIEW'
                WHEN 'TRIGGER'            THEN
                    'TRIGGER'
                WHEN 'SYNONYM'            THEN
                    'SYNONYM'
                WHEN 'MATERIALIZED VIEW'  THEN
                    'MATERIALIZED VIEW'
                WHEN 'JAVA CLASS'         THEN
                    'JAVA CLASS'
                WHEN 'JAVA SOURCE'        THEN
                    'JAVA SOURCE'
                ELSE
                    object_type
            END        AS compile_type,
            decode(object_type, 'TYPE SPECIFICATION', 1, 'TYPE BODY', 2,
                   'SYNONYM', 3, 'PACKAGE', 4, 'PACKAGE BODY',
                   5, 'FUNCTION', 6, 'PROCEDURE', 7,
                   'VIEW', 8, 'MATERIALIZED VIEW', 9, 'TRIGGER',
                   10, 'JAVA SOURCE', 11, 'JAVA CLASS', 12,
                   99) AS compile_order
        FROM
            all_objects
        WHERE
                status = 'INVALID'
            AND owner = user
            AND object_type IN ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'VIEW',
                                 'TRIGGER', 'TYPE SPECIFICATION', 'TYPE BODY', 'SYNONYM', 'MATERIALIZED VIEW',
                                 'JAVA CLASS', 'JAVA SOURCE' )
        ORDER BY
            compile_order,
            object_name
    ) LOOP
        BEGIN
            v_sql := 'ALTER '
                     || rec.compile_type
                     || ' "'
                     || rec.owner
                     || '"."'
                     || rec.object_name
                     || '" COMPILE';
-- Package/type bodies need the BODY keyword after COMPILE
            IF rec.object_type IN ( 'PACKAGE BODY', 'TYPE BODY' ) THEN
                v_sql := 'ALTER '
                         || replace(rec.compile_type, ' BODY', '')
                         || ' "'
                         || rec.owner
                         || '"."'
                         || rec.object_name
                         || '" COMPILE BODY';
            END IF;
            EXECUTE IMMEDIATE v_sql;
            v_count := v_count + 1;
            dbms_output.put_line('OK : '
                                 || rpad(rec.object_type, 20)
                                 || rec.owner
                                 || '.'
                                 || rec.object_name);
        EXCEPTION
            WHEN OTHERS THEN
                v_errors := v_errors + 1;
                dbms_output.put_line('FAIL : '
                                     || rpad(rec.object_type, 20)
                                     || rec.owner
                                     || '.'
                                     || rec.object_name);
                dbms_output.put_line(' ' || sqlerrm);
        END;
    END LOOP;
    dbms_output.put_line('---');
    dbms_output.put_line('Compiled: '
                         || v_count
                         || ' | Errors: '
                         || v_errors);
    dbms_output.put_line('End: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
-- Final status check
    dbms_output.put_line(' ');
    dbms_output.put_line('=== Remaining Invalid Objects ===');
    FOR rem IN (
        SELECT
            object_type,
            object_name
        FROM
            all_objects
        WHERE
                status = 'INVALID'
            AND owner = user
        ORDER BY
            object_type,
            object_name
    ) LOOP
        dbms_output.put_line(' '
                             || rpad(rem.object_type, 20)
                             || rem.object_name);
    END LOOP;
END;
/

Example Output:

=== Recompiling Invalid Objects for <USER> ===
Start: 2026-04-10 16:15:34
---
FAIL : VIEW                <USER>.<OBJECT>
 ORA-24344: success with compilation error
---
Compiled: 0 | Errors: 1
End: 2026-04-10 16:15:34
 
=== Remaining Invalid Objects ===
 VIEW                <OBJECT>
PL/SQL procedure successfully completed.

Multi-Pass Wrapper (Optional)

For deep or circular dependency chains, wrap the logic in a loop that repeats until the invalid count stops decreasing.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
    v_sql          VARCHAR2(4000);
    v_count        PLS_INTEGER;
    v_errors       PLS_INTEGER;
    v_prev_invalid PLS_INTEGER := 999999;
    v_cur_invalid  PLS_INTEGER;
    v_pass         PLS_INTEGER := 0;
BEGIN
    LOOP
        v_pass := v_pass + 1;
        v_count := 0;
        v_errors := 0;
        dbms_output.put_line('=== Pass '
                             || v_pass
                             || ' ===');
        FOR rec IN (
            SELECT
                owner,
                object_name,
                object_type,
                CASE object_type
                    WHEN 'TYPE SPECIFICATION' THEN
                        'TYPE'
                    WHEN 'TYPE BODY'          THEN
                        'TYPE BODY'
                    WHEN 'PACKAGE'            THEN
                        'PACKAGE'
                    WHEN 'PACKAGE BODY'       THEN
                        'PACKAGE BODY'
                    WHEN 'FUNCTION'           THEN
                        'FUNCTION'
                    WHEN 'PROCEDURE'          THEN
                        'PROCEDURE'
                    WHEN 'VIEW'               THEN
                        'VIEW'
                    WHEN 'TRIGGER'            THEN
                        'TRIGGER'
                    WHEN 'SYNONYM'            THEN
                        'SYNONYM'
                    WHEN 'MATERIALIZED VIEW'  THEN
                        'MATERIALIZED VIEW'
                    WHEN 'JAVA CLASS'         THEN
                        'JAVA CLASS'
                    WHEN 'JAVA SOURCE'        THEN
                        'JAVA SOURCE'
                    ELSE
                        object_type
                END        AS compile_type,
                decode(object_type, 'TYPE SPECIFICATION', 1, 'TYPE BODY', 2,
                       'SYNONYM', 3, 'PACKAGE', 4, 'PACKAGE BODY',
                       5, 'FUNCTION', 6, 'PROCEDURE', 7,
                       'VIEW', 8, 'MATERIALIZED VIEW', 9, 'TRIGGER',
                       10, 'JAVA SOURCE', 11, 'JAVA CLASS', 12,
                       99) AS compile_order
            FROM
                all_objects
            WHERE
                    status = 'INVALID'
                AND owner = user
                AND object_type IN ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'VIEW',
                                     'TRIGGER', 'TYPE SPECIFICATION', 'TYPE BODY', 'SYNONYM', 'MATERIALIZED VIEW',
                                     'JAVA CLASS', 'JAVA SOURCE' )
            ORDER BY
                compile_order,
                object_name
        ) LOOP
            BEGIN
                v_sql := 'ALTER '
                         || rec.compile_type
                         || ' "'
                         || rec.owner
                         || '"."'
                         || rec.object_name
                         || '" COMPILE';
                IF rec.object_type IN ( 'PACKAGE BODY', 'TYPE BODY' ) THEN
                    v_sql := 'ALTER '
                             || replace(rec.compile_type, ' BODY', '')
                             || ' "'
                             || rec.owner
                             || '"."'
                             || rec.object_name
                             || '" COMPILE BODY';
                END IF;
                EXECUTE IMMEDIATE v_sql;
                v_count := v_count + 1;
                dbms_output.put_line('OK : '
                                     || rpad(rec.object_type, 20)
                                     || rec.owner
                                     || '.'
                                     || rec.object_name);
            EXCEPTION
                WHEN OTHERS THEN
                    v_errors := v_errors + 1;
                    dbms_output.put_line('FAIL : '
                                         || rpad(rec.object_type, 20)
                                         || rec.owner
                                         || '.'
                                         || rec.object_name);
                    dbms_output.put_line(' ' || sqlerrm);
            END;
        END LOOP;
        dbms_output.put_line('Pass '
                             || v_pass
                             || ' — Compiled: '
                             || v_count
                             || ' Errors: '
                             || v_errors);
-- Count remaining invalid objects
        SELECT
            COUNT(*)
        INTO v_cur_invalid
        FROM
            all_objects
        WHERE
                status = 'INVALID'
            AND owner = user;
-- Exit if no improvement or nothing left
        EXIT WHEN v_cur_invalid = 0
        OR v_cur_invalid >= v_prev_invalid
        OR v_pass >= 5;
        v_prev_invalid := v_cur_invalid;
    END LOOP;
-- Final report
    dbms_output.put_line(' ');
    dbms_output.put_line('=== Final Status — Remaining Invalid Objects ===');
    FOR rem IN (
        SELECT
            object_type,
            object_name
        FROM
            all_objects
        WHERE
                status = 'INVALID'
            AND owner = user
        ORDER BY
            object_type,
            object_name
    ) LOOP
        dbms_output.put_line(' '
                             || rpad(rem.object_type, 20)
                             || rem.object_name);
    END LOOP;
END;
/

Recompile All Invalid User Objects

Notes

Variant

Main Recompile Script

Multi-Pass Wrapper (Optional)

Latest Posts

Tag Could