🧪 Invalid Objects

1COL owner FOR a20
2COL object_name FOR a50
3COL subobject_name FOR a30
4
5SELECT owner, object_name, subobject_name, object_type, created
6FROM dba_objects WHERE status <> 'VALID' ORDER BY 2;

1SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
2-- 61
3
4@?/rdbms/admin/utlrp
5
6SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
7-- 40

 1SET TERMOUT ON
 2SET SERVEROUTPUT ON
 3DECLARE
 4    CURSOR cur_invalid_objects IS
 5      SELECT object_name, object_type FROM user_objects
 6      WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER','SYNONYM','VIEW',
 7                            'MATERIALIZED VIEW','PACKAGE','PACKAGE BODY')
 8        AND status = 'INVALID';
 9    rec_columns cur_invalid_objects%ROWTYPE;
10    err_status  NUMBER;
11BEGIN
12    dbms_output.enable(10000);
13    OPEN cur_invalid_objects;
14    LOOP
15        FETCH cur_invalid_objects INTO rec_columns;
16        EXIT WHEN cur_invalid_objects%NOTFOUND;
17        BEGIN
18            IF rec_columns.object_type IN ('VIEW','SYNONYM','MATERIALIZED VIEW','PACKAGE') THEN
19                dbms_output.put_line('Recompiling ' || rec_columns.object_type || '  ' || rec_columns.object_name);
20                EXECUTE IMMEDIATE 'ALTER ' || rec_columns.object_type || ' "' || rec_columns.object_name || '" COMPILE';
21            ELSIF rec_columns.object_type = 'PACKAGE BODY' THEN
22                dbms_output.put_line('Recompiling ' || rec_columns.object_type || '  ' || rec_columns.object_name);
23                EXECUTE IMMEDIATE 'ALTER PACKAGE "' || rec_columns.object_name || '" COMPILE BODY';
24            ELSE
25                dbms_output.put_line('Recompiling ' || rec_columns.object_type || '  ' || rec_columns.object_name);
26                dbms_ddl.alter_compile(rec_columns.object_type, NULL, rec_columns.object_name);
27            END IF;
28        EXCEPTION WHEN OTHERS THEN
29            err_status := SQLCODE;
30            dbms_output.put_line('Recompilation failed: ' || SQLERRM(err_status));
31        END;
32    END LOOP;
33    CLOSE cur_invalid_objects;
34END;
35/

 1SET SERVEROUTPUT ON
 2DECLARE
 3    CURSOR cur_invalid_objects IS
 4      SELECT object_name, object_type FROM user_objects
 5      WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER','SYNONYM','VIEW',
 6                            'MATERIALIZED VIEW','PACKAGE','PACKAGE BODY')
 7        AND status = 'INVALID';
 8    rec_columns cur_invalid_objects%ROWTYPE;
 9BEGIN
10    dbms_output.enable(10000);
11    OPEN cur_invalid_objects;
12    LOOP
13        FETCH cur_invalid_objects INTO rec_columns;
14        EXIT WHEN cur_invalid_objects%NOTFOUND;
15        dbms_output.put_line('DROP ' || rec_columns.object_type || ' ' || rec_columns.object_name);
16        EXECUTE IMMEDIATE 'DROP ' || rec_columns.object_type || ' ' || rec_columns.object_name;
17    END LOOP;
18    CLOSE cur_invalid_objects;
19END;
20/