Overview

exec utl_recomp.recomp_serial performs compilation only for objects in INVALID state.

In an operational environment, even if the DB object status is VALID,

• the procedure/package on the AP side does not operate normally, or

• after redistribution/patch/migration, execution logic mismatch is suspected

it may be necessary to recompile VALID state PSMs (procedures, functions, triggers, packages, etc.).

In this case, using the FORCE option, you can perform forced recompilation including VALID PSMs.

Method

Operation Method 

1) Default recomp operation

• utl_recomp.recomp_serial

  • Performs compilation only for INVALID objects

  • Does not perform recompilation logic on VALID state PSMs.

2) FORCE recompilation operation

• When using the FORCE option

  • Even PSMs that were previously in VALID state are forcibly recompiled

• Target scope

  • procedure / function / trigger / package / object type (PSM)

  • VIEW (FORCE not supported)

※ Since VIEW is not a target for FORCE recompilation,
when an INVALID VIEW occurs, only re-query (select) is performed instead of recompilation.

Method 1) Force full recompilation with FORCE option (recommended)

conn sys
alter system recompile all force;

Method 2) FORCE operation using hidden parameter

conn sys
alter system set _DDL_PSM_RECOMPILE_FORCE = Y;
alter system recompile all;
alter system set _DDL_PSM_RECOMPILE_FORCE = N;

Check parameter

select * from vt_parameter
where name = '_DDL_PSM_RECOMPILE_FORCE';

Verification

1) Check INVALID objects

select owner, object_type, object_name, status
from dba_objects
where status <> 'VALID'
order by 1,2,3;

2) Perform FORCE recompilation

alter system recompile all force;

3) Recheck

select owner, object_type, object_name, status
from dba_objects
where status <> 'VALID'
order by 1,2,3;