Skip to content

Useful Scripts for compiling invalids in Oracle database

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;
/
Brijesh Gogia
Leave a Reply