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;