Recompile the invalid object for oracle.

1. How does the invalid object come?

The Oracle database will invalidate objects if a dependent object is changed. If I rebuild a table, the indexes on that table will become invalid because they use the table'srowids and rebuilding the table changes a row's rowid. It is the same with objects like packages, procedures and functions. 

 

2.Build-in scripts to recompile the invalid objects.

We now have a supported script utlrp.sql located in the $ORACLE_HOME/rdbms/admin/utlrp.sql to do recompile for us.

Below code will list out all the current invalid object and run utlrp.sql to compile all of them.

COLUMN owner format a30
COLUMN object_name format a30 wrap
COLUMN object_type format a30 wrap

SET pages 56 lines 130 feedback off echo off
TTITLE "Report of Invalid Objects In Database" skip 2
SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE status = 'INVALID';
PROMPT "Will now run utlrp.sql to try to recompile objects"
@?/rdbms/admin/utlrp.sql

The following PL/SQL block invokes UTL_RECOMP to recompile invalid objects in the database.
Recompilation time is proportional to the number of invalid objects in the database,
so this command may take a long time to execute on a database with a large number of invalid objects.

Use the following queries to track recompilation progress:

1. Query returning the number of invalid objects remaining. This number should decrease with time.
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

2. Query returning the number of objects compiled so far. This number should increase with time.
SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

This script automatically chooses serial or parallel recompilation based on the number of CPUs
available (parameter cpu_count) multiplied by the number of threads per CPU (parameter parallel_threads_per_cpu).
On RAC, this number is added across all RAC nodes.

UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel recompilation. Jobs are created without instance
affinity so that they can migrate across RAC nodes. Use the following queries to verify
whether UTL_RECOMP jobs are being created and run correctly:

1. Query showing jobs created by UTL_RECOMP
SELECT job_name FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';

2. Query showing UTL_RECOMP jobs that are running
SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';

Below is a sql with a good format to list all the invalid database object.

break on c1 skip 2
set pages 999
col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10
ttitle 'Invalid|Objects'
select 
   owner       c1, 
   object_type c3,
   object_name c2
from 
   dba_objects 
where 
   status != 'VALID'
order by
   owner,
   object_type;

 

3. Recompile with UTL_RECOMP package

EXEC UTL_RECOMP.recomp_serial('schema name');

 

4. compile command for individual object.

For function:

alter function gpcomp1.fn_load_notes_from_jde compile;

For procedure:

alter procedure gpcomp1.updatetemplate compile;

For view

alter view gpcomp1.gamatchedcashapplied compile;

For public synonym, which can only be recompiled by sys

alter public synonym  gpcrfcode  compile

 

Here is a script to recompile invalid PL/SQL packages and package bodies. 

You may need to run it more than once for dependencies, if you get errors from the script.

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql

check the status of oracle component.

select comp_id,  comp_name,  version, status, namespace, schema from dba_registry;