Detailed Description:
The compile_invalid_objects.sh script is designed to automate the identification and compilation of invalid objects across pluggable databases (PDBs) within an Oracle multitenant database environment. Invalid objects—such as packages, procedures, functions, triggers, views, types, and synonyms—can arise due to changes in underlying objects, dependencies, or after migrations and patching. This script provides a robust and repeatable mechanism to recompile these objects to ensure schema consistency and application functionality.
Key Features:
- Instance and PDB Aware: Accepts input parameters for target database instance, PDB, and schema name to provide scoped compilation.
- Schema-Specific Compilation: Can focus on a specific schema or compile across all schemas when no schema is specified.
- Read-Write Mode Check: Verifies that the database is in READ WRITE mode before proceeding, ensuring changes can be applied.
- Session Container Switching: Iterates through all PDBs in READ WRITE mode and alters session container context for cross-PDB compatibility.
- Selective Object Types: Targets only compilable object types such as PACKAGE, PACKAGE BODY, TRIGGER, VIEW, PROCEDURE, FUNCTION, TYPE, and TYPE BODY.
- Indicator File Mechanism: Uses a .ind file to prevent concurrent execution of the same job for a given instance.
- Logging: Logs are maintained in a dedicated directory structure for auditing and troubleshooting purposes.
- Safe Execution: Compilation commands are echoed for visibility; execution lines are commented (–EXECUTE IMMEDIATE v_sql;) for dry-run by default, and can be enabled as needed.
- Error Handling: Captures and logs SQL execution failures without halting the entire script.
Usage Syntax:
compile_invalid_objects.sh -i <INSTANCE_NAME> -p <PDB_NAME> -o <SCHEMA_NAME>
Example:
./compile_invalid_objects.sh -i DEVDB -p SALES_PDB -o HR
Script:
###############################################################################
# File Name : compile_invalid_objects.sh
# Author : Vimal Raju
# Date : 14/08/2025
# Description : Compile invalids objects
#
###############################################################################
#############################################################
#
# Display usage infommation
#
usage () {
echo ” ”
echo ” Compile the invalids objects ”
echo ” -h Display this message”
echo ” -i Specify the target database instance name ”
echo ” -p Specify the target database PDB name ”
echo ” -o Specify the target database schema name ”
echo ” Example : compile_invalid_objects.sh -i DEV -p TEST -o SCOTT ”
exit 1
}
exitError1 () {
case ${DBA_TRACE} in
Y|y) set -x ;;
esac
if [ -f ${INDFILE} ]; then
rm ${INDFILE}
fi
exit 1
}
validate() {
if [ ${#TARGET_INST} -eq 0 ]; then
echo “No Target instance specified!”
usage
fi
INDFILE=${IND_LOC}/${TARGET_INST}_compile_invalid_objects.ind
# To check if the indicator file is there
if [ -f ${INDFILE} ]; then
echo “Indicator file ${INDFILE} exists!”
exit 1
else
touch ${INDFILE}
fi
if [ -f ${LOG_LOC}/${TARGET_INST}_compile_invalid_objects_${DATE}.log ]; then
echo “”
else
touch ${LOG_LOC}/${TARGET_INST}_compile_invalid_objects_${DATE}.log
fi
db_status=$(sqlplus -s / as sysdba << EOF
set lines 170 pages 0 head off feed off termout off verify off
select open_mode from v\$database;
exit
EOF
)
if [ “${db_status}” != “READ WRITE” ]
then
echo ” ${TARGET_INST} is Down ”
exitError1
fi
}
compile_invalid_objects() {
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pagesize 0 heading off feedback off lines 200
SET SERVEROUTPUT ON
DECLARE
CURSOR pdbs IS
SELECT name FROM v\$pdbs WHERE open_mode = ‘READ WRITE’;
v_sql CLOB;
BEGIN
FOR pdb_rec IN pdbs LOOP
DBMS_OUTPUT.PUT_LINE(‘———————————————–‘);
DBMS_OUTPUT.PUT_LINE(‘Compiling in PDB: ‘ || pdb_rec.name);
DBMS_OUTPUT.PUT_LINE(‘———————————————–‘);
EXECUTE IMMEDIATE ‘ALTER SESSION SET CONTAINER = ‘ || pdb_rec.name;
FOR obj IN (
SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’,’SYNONYM’,’TRIGGER’,’VIEW’,’PROCEDURE’,’FUNCTION’,’TYPE’,’TYPE BODY’)
AND status != ‘VALID’
AND (UPPER(owner) = UPPER(‘${SCHEMA_NAME}’) OR ‘${SCHEMA_NAME}’ IS NULL)
ORDER BY 1,3
) LOOP
BEGIN
IF obj.object_type = ‘PACKAGE BODY’ THEN
v_sql := ‘ALTER PACKAGE “‘ || obj.owner || ‘”.”‘ || obj.object_name || ‘” COMPILE BODY’;
ELSIF obj.object_type = ‘TYPE BODY’ THEN
v_sql := ‘ALTER TYPE “‘ || obj.owner || ‘”.”‘ || obj.object_name || ‘” COMPILE BODY’;
ELSE
v_sql := ‘ALTER ‘ || obj.object_type || ‘ “‘ || obj.owner || ‘”.”‘ || obj.object_name || ‘” COMPILE’;
END IF;
DBMS_OUTPUT.PUT_LINE(v_sql);
–EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Failed: ‘ || v_sql);
END;
END LOOP;
END LOOP;
END;
/
EOF
}
TARGET_INST=
PDB_NAME=
SCHEMA_NAME=
MAIL_TO=globusdba@doyensys.com
DATE1=`date +”%d-%b-%Y %T”`
while getopts :hi:p:o: opt $*
do
case $opt in
- h) usage
;;
- i) TARGET_INST=${OPTARG}
echo TARGET_INST set to ${TARGET_INST}
;;
- p) PDB_NAME=${OPTARG}
echo PDB_NAME set to ${PDB_NAME}
;;
- o) SCHEMA_NAME=${OPTARG}
echo SCHEMA_NAME set to ${SCHEMA_NAME}
;;
*) echo “******************************************************************”
echo “${JOBNAME}: Invalid option found on command line: ${OPTARG}”
echo “******************************************************************”
usage
;;
esac
done
. ~/.bash_profile
. ~/oracle.env
DBA_HOME=/home/oracle
LOG_LOC=${DBA_HOME}/log
TMP_LOC=${DBA_HOME}/tmp
IND_LOC=${DBA_HOME}/ind
validate
compile_invalid_objects
if [ -f ${INDFILE} ]; then
rm -f ${INDFILE}
fi
