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;
/

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud and AI Architect with over 19 years in Oracle Applications, Databases, and Cloud ecosystems. My work spans large-scale, global projects where I’ve designed and optimized solutions across both Oracle and non-Oracle stacks—on-prem and in the cloud. Lately, my focus has shifted toward integrating AI into enterprise architectures, leveraging GenAI, automation, and data intelligence to drive performance, resilience, and modernization. I’m passionate about exploring emerging AI tools, agentic workflows, and practical use cases—and I'm fortunate that my role gives me space to experiment and share my learnings.

Useful Scripts for compiling invalids in Oracle database

Related posts: