Automating Oracle Invalid Object Recompilation: A Practical Script

As Oracle DBAs, we frequently encounter situations where database objects become invalid after patches, upgrades, or schema changes. Manually recompiling these objects can be time-consuming, especially in large databases. Today I’ll share a practical script that automates the generation of recompilation commands for all invalid objects in your Oracle database.

The Problem

When objects become invalid, they need to be recompiled before they can be used. While Oracle automatically attempts to recompile objects when they’re accessed, it’s often better to proactively recompile them to avoid runtime errors and performance issues.

The Solution: pvm_pre_change.sql

This script generates a dynamic SQL file that contains all the necessary ALTER COMPILE statements for your invalid objects. Here’s how it works:

/********************************************
     Author: Paresh Mehta
     Script Name: pvm_pre_change.sql
********************************************/

column currdate new_value pvmdt noprint
select 'pvm_comp_' || to_char(SYSDATE, 'DDMONRRRR_HH24MI') || '.sql' currdate from v$instance;

spool &pvmdt

set echo off
set verify off
set lines 120 pages 2000
set head off
set time off timing off
set feedback off
prompt set pages 5000 lines 120
prompt select 'prompt '||a.owner||'.'||a.object_name||' - '||a.object_type|| chr(10)||
prompt 'alter '||
prompt decode(a.object_type,'PACKAGE BODY','PACKAGE',object_type) || ' "'||
prompt a.owner||'".'||a.object_name||' compile '||
prompt decode(a.object_type,'PACKAGE BODY','BODY;',';')
prompt from
prompt dba_objects a
prompt where   a.status = 'INVALID'
prompt and object_id not in
(
exec dbms_output.enable(NULL);
set serveroutput on size 1000000
declare
  v_objid number(10);
  total_count number(10);
  v_counter number(10) := 1;
  cursor c1 is select object_id from dba_objects where status='INVALID';
begin
  select count(1) into total_count from dba_objects where status='INVALID';
  for cur1 in c1 loop
      dbms_output.put_line(cur1.object_id);
      if v_counter < total_count then
         dbms_output.put_line(',');
      end if;
      v_counter := v_counter + 1;
  end loop;
end;
/
prompt )
prompt /

set feedback on

spool off

How to Use This Script

1. Run the script: Execute pvm_pre_change.sql in SQL*Plus while connected as a privileged user (like SYSDBA)
2. Review the output: The script generates a file named pvm_comp_[timestamp].sql
3. Execute the generated script: Run the generated file to recompile all invalid objects

Key Features

1. Dynamic filename: The output file includes a timestamp to avoid overwriting previous runs
2. Comprehensive object handling: Handles all object types including packages, package bodies, views, etc.
3. Proper formatting: Generates well-formatted SQL with prompts showing which object is being compiled
4. Efficient processing: Uses PL/SQL to dynamically build the list of invalid objects

Why This Approach Works Well

• Automation: Saves time by generating all recompilation commands at once
• Documentation: The output file serves as a record of what was recompiled
• Flexibility: You can review the generated script before executing it
• Safety: The script only targets invalid objects

Best Practices

1. Always review the generated script before executing it
2. Run this during maintenance windows as recompilation may cause brief locks
3. Consider running this after major database changes
4. Monitor the recompilation process for any errors

This script has been a valuable tool in my DBA toolkit, helping to quickly resolve invalid object issues across various Oracle environments. Give it a try in your environment and let me know how it works for you!