Here are a few scripts that can be used to compile invalid objects in an Oracle database:
Note: Please make sure that you have the right permissions to execute these scripts and also replace the placeholder ‘<schema_name>’ with the actual name of the schema you want to compile.
Compiling a specific package:
ALTER PACKAGE <schema_name>.<package_name> COMPILE;
Compiling a specific package body:
ALTER PACKAGE BODY <schema_name>.<package_name> COMPILE;
Compiling a specific function:
ALTER FUNCTION <schema_name>.<function_name> COMPILE;
Compiling a specific procedure:
ALTER PROCEDURE <schema_name>.<procedure_name> COMPILE;
Compiling a specific trigger:
ALTER TRIGGER <schema_name>.<trigger_name> COMPILE;
Compiling a specific view:
ALTER VIEW <schema_name>.<view_name> COMPILE;
Compiling all invalid objects in the entire database:
BEGIN FOR i IN (SELECT object_name, object_type FROM dba_objects WHERE status='INVALID') LOOP BEGIN IF i.object_type='PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE'; ELSIF i.object_type='PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE BODY '||i.object_name||' COMPILE'; ELSIF i.object_type='FUNCTION' THEN EXECUTE IMMEDIATE 'ALTER FUNCTION '||i.object_name||' COMPILE'; ELSIF i.object_type='PROCEDURE' THEN EXECUTE IMMEDIATE 'ALTER PROCEDURE '||i.object_name||' COMPILE'; ELSIF i.object_type='TRIGGER' THEN EXECUTE IMMEDIATE 'ALTER TRIGGER '||i.object_name||' COMPILE'; ELSIF i.object_type='VIEW' THEN EXECUTE IMMEDIATE 'ALTER VIEW '||i.object_name||' COMPILE'; END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered while compiling '||i.object_name||': '||sqlerrm); END; END LOOP; END; /
Compiling all invalid objects in a specific schema:
BEGIN FOR i IN (SELECT object_name, object_type FROM dba_objects WHERE status='INVALID' AND owner='<schema_name>') LOOP BEGIN IF i.object_type='PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE'; ELSIF i.object_type='PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE BODY '||i.object_name||' COMPILE'; ELSIF i.object_type='FUNCTION' THEN EXECUTE IMMEDIATE 'ALTER FUNCTION '||i.object_name||' COMPILE'; ELSIF i.object_type='PROCEDURE' THEN EXECUTE IMMEDIATE 'ALTER PROCEDURE '||i.object_name||' COMPILE'; ELSIF i.object_type='TRIGGER' THEN EXECUTE IMMEDIATE 'ALTER TRIGGER '||i.object_name||' COMPILE'; ELSIF i.object_type='VIEW' THEN EXECUTE IMMEDIATE 'ALTER VIEW '||i.object_name||' COMPILE'; END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered while compiling '||i.object_name||': '||sqlerrm); END; END LOOP; END; /
Compiling all invalid objects in a specific schema, with output of success and error messages:
DECLARE CURSOR invalid_objects IS SELECT object_name, object_type FROM dba_objects WHERE status='INVALID' AND owner='<schema_name>'; BEGIN FOR i IN invalid_objects LOOP BEGIN IF i.object_type='PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); ELSIF i.object_type='PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE BODY '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); ELSIF i.object_type='FUNCTION' THEN EXECUTE IMMEDIATE 'ALTER FUNCTION '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); ELSIF i.object_type='PROCEDURE' THEN EXECUTE IMMEDIATE 'ALTER PROCEDURE '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); ELSIF i.object_type='TRIGGER' THEN EXECUTE IMMEDIATE 'ALTER TRIGGER '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); ELSIF i.object_type='VIEW' THEN EXECUTE IMMEDIATE 'ALTER VIEW '||i.object_name||' COMPILE'; dbms_output.put_line(i.object_name||' was successfully compiled'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered while compiling '||i.object_name||': '||sqlerrm); END; END LOOP; END; /
Compiling invalid objects using DBMS_UTILITY.COMPILE_SCHEMA:
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(schema => '<schema_name>', compile_all => TRUE);
Compiling invalid objects using UTL_RECOMP:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(schema => '<schema_name>');
Compiling invalid objects using a shell script:
#!/bin/bash # Connect to the database sqlplus -S / as sysdba << EOF # Set the serveroutput on SET SERVEROUTPUT ON; # Compile invalid objects DECLARE CURSOR invalid_objects IS SELECT object_name, object_type FROM dba_objects WHERE status='INVALID'; BEGIN FOR i IN invalid_objects LOOP BEGIN IF i.object_type='PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE'; ELSIF i.object_type='PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE BODY '||i.object_name||' COMPILE'; ELSIF i.object_type='FUNCTION' THEN EXECUTE IMMEDIATE 'ALTER FUNCTION '||i.object_name||' COMPILE'; ELSIF i.object_type='PROCEDURE' THEN EXECUTE IMMEDIATE 'ALTER PROCEDURE '||i.object_name||' COMPILE'; ELSIF i.object_type='TRIGGER' THEN EXECUTE IMMEDIATE 'ALTER TRIGGER '||i.object_name||' COMPILE'; ELSIF i.object_type='VIEW' THEN EXECUTE IMMEDIATE 'ALTER VIEW '||i.object_name||' COMPILE'; END IF; dbms_output.put_line(i.object_name||' was successfully compiled'); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered while compiling '||i.object_name||': '||sqlerrm); END; END LOOP; END; / # Exit the SQL*Plus EXIT; EOF
Compiling invalid objects using a SQL script with a loop for all schemas
BEGIN FOR i IN (SELECT username FROM dba_users) LOOP EXECUTE IMMEDIATE 'ALTER SCHEMA '||i.username||' COMPILE'; END LOOP; END; /
- Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
- Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
- Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024