Create DDL to recompile invalid objects. Note: spools to r.lst
SQL> @recomp
Session altered.
Enter # for current user
Owner : TOOLBOX
User value accepted.
Invalid objects owned by: TOOLBOX
spool r.lst
alter PACKAGE "TOOLBOX"."DB_DPA_DATA_UPD_AUDIT" compile body;
sho err package body TOOLBOX.DB_DPA_DATA_UPD_AUDIT
alter PACKAGE "TOOLBOX"."P_ACCS_COMMUNICATION_DEV_4" compile body;
sho err package body TOOLBOX.P_ACCS_COMMUNICATION_DEV_4
alter PACKAGE "TOOLBOX"."P_ACCS_COMMUNICATION_DEV_5" compile body;
sho err package body TOOLBOX.P_ACCS_COMMUNICATION_DEV_5
alter PACKAGE "TOOLBOX"."P_ACCS_CREATE_TRANS_1" compile;
sho err PACKAGE TOOLBOX.P_ACCS_CREATE_TRANS_1
alter PACKAGE "TOOLBOX"."P_ACCS_CREATE_TRANS_1" compile body;
sho err package body TOOLBOX.P_ACCS_CREATE_TRANS_1
alter PACKAGE "TOOLBOX"."P_ACCS_CREATE_TRANS_APP_1" compile;
sho err PACKAGE TOOLBOX.P_ACCS_CREATE_TRANS_APP_1
alter PACKAGE "TOOLBOX"."P_ACCS_CREATE_TRANS_APP_1" compile body;
sho err package body TOOLBOX.P_ACCS_CREATE_TRANS_APP_1
alter PACKAGE "TOOLBOX"."P_ACCS_DQM_DEBUG_1" compile body;
sho err package body TOOLBOX.P_ACCS_DQM_DEBUG_1
alter PACKAGE "TOOLBOX"."P_ACCS_DEV_BOOKING_DET_1" compile body;
sho err package body TOOLBOX.P_ACCS_DEV_BOOKING_DET_1
alter PACKAGE "TOOLBOX"."P_ACCS_DEV_EVENT_1" compile body;
sho err package body TOOLBOX.P_ACCS_DEV_EVENT_1
alter PACKAGE "TOOLBOX"."P_ACCS_INS_ICW_MLA_FB" compile body;
sho err package body TOOLBOX.P_ACCS_INS_ICW_MLA_FB
alter PACKAGE "TOOLBOX"."P_ACCS_CONTACT_DEV_4" compile body;
sho err package body TOOLBOX.P_ACCS_CONTACT_DEV_4
alter PACKAGE "TOOLBOX"."P_ACCS_ME_EXP_BAL" compile body;
sho err package body TOOLBOX.P_ACCS_ME_EXP_BAL
alter PACKAGE "TOOLBOX"."P_ACCS_PM_EPC_RUN" compile body;
sho err package body TOOLBOX.P_ACCS_PM_EPC_RUN
alter PACKAGE "TOOLBOX"."P_ACCS_TM_COMMON_VAL_CTD" compile body;
sho err package body TOOLBOX.P_ACCS_TM_COMMON_VAL_CTD
alter PACKAGE "TOOLBOX"."P_ACCS_TM_CTD_RETRO" compile body;
sho err package body TOOLBOX.P_ACCS_TM_CTD_RETRO
alter PACKAGE "TOOLBOX"."P_ACCS_TM_ETL_LOAD" compile body;
sho err package body TOOLBOX.P_ACCS_TM_ETL_LOAD
alter PACKAGE "TOOLBOX"."P_ACCS_TM_LOAD_DATA_3" compile body;
sho err package body TOOLBOX.P_ACCS_TM_LOAD_DATA_3
alter PACKAGE "TOOLBOX"."P_UPS_CHECK_ACC_OPEN_1" compile;
sho err PACKAGE TOOLBOX.P_UPS_CHECK_ACC_OPEN_1
alter PACKAGE "TOOLBOX"."P_UPS_CHECK_ACC_OPEN_1" compile body;
sho err package body TOOLBOX.P_UPS_CHECK_ACC_OPEN_1
alter PACKAGE "TOOLBOX"."P_UPS_CONTACT_DEV_4" compile body;
sho err package body TOOLBOX.P_UPS_CONTACT_DEV_4
spool off
SQL>
prompt
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'
/
set echo off
set verify off
set lines 400
set pages 999
set head off
set feed off
set serveroutput on size 1000000 format wrap
set trimspool on
set long 10000
break on owner skip 1
variable v_user varchar2( 30 );
prompt Enter # for current user
prompt
accept x_owner prompt 'Owner : '
declare
cursor c_user is select count(*) from dba_users where username = nvl( :v_user, username );
ln_count number;
begin
if '&&x_owner' = '#' then
:v_user := user;
else
:v_user := upper( '&&x_owner' );
end if;
open c_user; fetch c_user into ln_count; close c_user;
if ln_count = 0 then
dbms_output.put_line( 'User not found : ' || upper( :v_user ) );
else
dbms_output.put_line( 'User value accepted.' );
end if;
if :v_user is null then
dbms_output.put_line( 'All invalid objects ...' );
else
dbms_output.put_line( 'Invalid objects owned by: ' || :v_user );
end if;
end;
/
spool r.lst
prompt spool r_spool.lst
select
DECODE( object_type,
'SYNONYM',
decode( owner,
'PUBLIC', 'desc ' || object_name,
'desc ' || chr(34) || owner || chr(34) || '.' || chr(34) || object_name || chr(34) ),
'PACKAGE BODY',
'alter PACKAGE ' || chr(34) || owner || chr(34) || '.' || chr(34) || object_name || chr(34) || ' compile body;' || chr(10) ||
'sho err package body ' || owner || '.' || object_name,
'TYPE',
'alter type ' || chr(34) || owner || chr(34) || '.' || chr(34) || object_name || chr(34) || ' reset;' || chr(10) ||
'sho err type ' || owner || '.' || object_name,
'TYPE BODY',
'alter type ' || chr(34) || owner || chr(34) || '.' || chr(34) || object_name || chr(34) || ' compile body;' || chr(10) ||
'sho err type body ' || owner || '.' || object_name,
'alter ' || OBJECT_TYPE || ' ' ||
chr(34) || owner || chr(34) || '.' || chr(34) || object_name || chr(34) || ' compile;' || chr(10) ||
'sho err ' || object_type || ' ' || owner || '.' || object_name
) || chr(10)
from dba_objects
where owner = nvl( :v_user, owner )
and instr( object_name, '==' ) = 0
and ( status is null or status <> 'VALID' )
order by owner, object_name
/
prompt spool off
spool off
clear breaks
set head on
set feed on