Recompile the invalid object for Oracle.

Source: Internet
Author: User

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.