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/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Useful Scripts for compiling invalids in Oracle database

Related posts: