Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • default permission on ~/.ssh/authorized_keys2 or authorized_keys Linux/Unix
  • Generate SSH without password authentication. Linux/Unix
  • CPU speed on solaris Linux/Unix
  • Identical Dblink Issue… Oracle
  • How to see which patches are applied. Oracle
  • SQL Server: How to see current transactions or requests SQL Server
  • Good links for x$ tables in oracle. Oracle
  • Trace a SQL session from another session using ORADEBUG Oracle
  • How to find where datafile is created dbf_info.sql Oracle
  • ORA-4031 issue and solution on 09-MAY-2008 Oracle
  • CPU Core related projections AWS
  • pvm_pre_change.sql Oracle
  • Another export with Query Oracle
  • Move WordPress site from one hosting service to other. PHP/MYSQL/Wordpress
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix

pvm_pre_change.sql

Posted on 08-Apr-2026 By Admin

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!

Oracle, SQL scripts

Post navigation

Previous Post: find_encr_wallet.sql
Next Post: find_arc.sql

Related Posts

  • How to see which patches are applied. Oracle
  • sess_server.sql Oracle
  • pvm_metric.sql for gathering report from vmstat tables Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • Temporary Tablespsace Temp tablespace behaviour Oracle
  • Changing the Global Database Name Oracle

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema) Oracle
  • Giving Grant on v$DATABASE Oracle
  • How to check current redo log progress redo_progress.sql Oracle
  • Running select from V$ views from remote server Linux/Unix
  • replace alphabets using sed Linux/Unix
  • Tutornet.US — Source of excellent learning Uncategorized
  • Complete Git Tutorial for Beginners GIT
  • sid_wise_cursor.sql find open cursor basis on username or SID Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme