Recompile the invalid object for oracle.

標籤：des   style   blog   color   os   strong   io   for   

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 a30COLUMN object_name format a30 wrapCOLUMN object_type format a30 wrapSET pages 56 lines 130 feedback off echo offTTITLE "Report of Invalid Objects In Database" skip 2SELECT 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 2set pages 999col c1 heading ‘owner‘ format a15col c2 heading ‘name‘ format a40col c3 heading ‘type‘ format a10ttitle ‘Invalid|Objects‘select    owner       c1,    object_type c3,   object_name c2from    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;