-- Oracle Database Invalid Objects can be recompiled using pl/sql block -- or a procedure as shown below. CREATE OR REPLACE PROCEDURE Compile_All_Invalid(o_status OUT VARCHAR2) AS CURSOR c_inv_obj IS SELECT ao.owner FROM all_objects ao WHERE NOT EXISTS (SELECT NULL FROM v$access a, v$session s WHERE a.sid = s.sid AND a.object = ao.object_name) AND ao.object_type IN ('FUNCTION','PROCEDURE','PACKAGE','TYPE','MATERIALIZED VIEW') AND ao.status = 'INVALID'; BEGIN FOR obj_rec IN c_inv_obj LOOP DBMS_UTILITY.COMPILE_SCHEMA(schema => obj_rec.owner, compile_all => TRUE, reuse_settings => TRUE); END LOOP; EXCEPTION WHEN OTHERS THEN o_status := SQLERRM; RAISE; END Compile_All_Invalid; / -- If an object is being used by an application, ETL process or any BI -- report, then it will lock the session when it tries to compile the -- locked object. The above code will avoid the session from being -- locked when compiling and compile all currently inactive objects. -- Materialized views take long to recompile and can be executed separately DECLARE CURSOR c_inv_obj IS SELECT ao.owner FROM all_objects ao WHERE NOT EXISTS (SELECT NULL FROM v$access a, v$session s WHERE a.sid = s.sid AND a.object = ao.object_name) AND ao.object_type = 'MATERIALIZED VIEW' AND ao.status = 'INVALID'; BEGIN FOR obj_rec IN c_inv_obj LOOP DBMS_UTILITY.COMPILE_SCHEMA(schema => obj_rec.owner, compile_all => TRUE, reuse_settings => TRUE); END LOOP; EXCEPTION WHEN OTHERS THEN o_status := SQLERRM; RAISE; END Compile_All_Invalid; /
22168