Oracle invalid objects in the schema

Operations such as upgrades, patches or DDL changes can invalidate schema objects. These invalid objects can be manually recompiled to re-validate. If recompilation fails with errors we have to check all the complex dependencies

1. Please check the DB status.

Set lin 500
Col HOST_NAME for A30
Set pages 500
select inst_id,instance_number, instance_name, host_name, to_char(STARTUP_TIME,'dd/mon/yyyy hh24:mi:ss') "STARTUP", status, archiver, database_status, instance_role,active_state,open_mode, database_role from gv$instance,v$database order by 1 ;

2. DBA_OBJECTS view can be used to identify invalid objects using the following query.

select owner,object_type,count(*) from dba_objects where status='INVALID' and owner='&schema_owner' group by owner,object_type;

Example:

OWNER OBJECT_TYPE COUNT(*)
—————————— ——————- ———-
CELL_CLI PACKAGE BODY 51
CELL_CLI DIMENSION 16
CELL_CLI VIEW 7
CELL_CLI PACKAGE 52

3. Recompiling the invalid objects:

  • If we have small numbers of invalid objects, we can manually re-compile them using
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
  • If we have more invalid objects, we can use UTL_RECOMP package that recompiles all schema level invalid objects here in serial order.
SQL> EXEC UTL_RECOMP.recomp_serial('schema_name');
PL/SQL procedure successfully completed.
Tags: ,

Leave a Reply