Recompile the invalid object for Oracle.

1. How does the invalid object come?

The Oracle database would invalidate objects if a dependent object is changed. If I rebuild A table, the indexes on that table would become invalid because they use the table ' srowids and Rebuil Ding 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 are now having a supported script Utlrp.sql located in the $ORACLE _home/rdbms/admin/utlrp.sql to doing recompile for us.

Below code would list out all the invalid object and run Utlrp.sql to compile all of them.

COLUMNowner Format A30COLUMN object_nameformat A30 WrapCOLUMNobject_type format A30 wrapSETPages AboutLines theFeedbackoffEchooffTtitle " report ofInvalid Objectsinch DatabaseThe Skip2SELECTOwnerobject_name, Object_type fromdba_objectsWHEREStatus= 'INVALID'; PROMPT "would now run Utlrp.sql toTry torecompile 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 is 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 is 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 is 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 was added across all RAC nodes.

Utl_recomp uses Dbms_scheduler to the create jobs for parallel recompilation. Jobs is created without instance
Affinity So, they can migrate across RAC nodes. Use the following queries to verify
Whether Utl_recomp jobs is 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 is 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  onC1 Skip2SetPages999Col C1 heading'owner'format a15col c2 heading'name'Format a40col c3 heading'type'format A10ttitle'invalid| Objects'Selectowner C1, object_type C3,object_nameC2 fromdba_objectswhereStatus!= 'VALID'Order  byowner, 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 is recompiled by SYS

Alter public synonym Gpcrfcode compile

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

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.

Selectcomp_id,Comp_name,version,status,namespace,Schema fromDba_registry;