compile invalids

// get_dependencies.sql

// dependencies for packages etc

SELECT distinct referenced_name, referenced_type
  FROM  dba_dependencies
  WHERE
    REFERENCED_OWNER=upper('&owner')
  and name =upper('&objname') order by referenced_type ;

//
exec dbms_utility.get_dependency('PACKAGE BODY', 'SCOTT','MYPKG');

// get_ddl.sql
SET LONG 2000000
SET PAGESIZE 0
select DBMS_METADATA.GET_DDL('PACKAGE_BODY','MYPKG') from dual;

// gen_compile.sql

set pages 0
spool gen_compile.out
accept inuser char prompt 'Enter Userid: '
select 'alter '||case when object_type='PACKAGE BODY' then 'PACKAGE' else object_type end||' '||owner||'.'||object_name||' compile;'
from dba_objects
where owner like upper('&inuser')
and status='INVALID'
order by object_type
/
spool off

//  count_invalids.sql

rem
ttitle 'Count of Invalid Objects'
rem
col owner format a8 heading 'OWNER'
col invalids format 999,999 heading '# of INVALIDS OBJECTS'
break on owner skip 1
rem
select owner,
       object_type,
       count(*)  invalids
  from   dba_objects
 where  owner like upper('&owner')
  and   status ='INVALID'
 group by rollup(owner, object_type);rem  count_invalids.sql



//  list_invalids.sql

rem
set time on
set timing on
set echo off
set feedback on
rem
break on owner on type skip 1
rem
select substr(owner,1,12) owner
       ,substr(object_type,1,30) type
       ,substr(object_name,1,30) object
       ,status
  from
    dba_objects
 where
   owner like upper('&owner')
 and
   object_type like upper('&type')
 and
   status <> 'VALID'
 order by owner,object_type,object_name;
rem
rem end of script


// object not recompiling as it is in use

   COLUMN lock_id2 FORMAT A30
   select to_char(SESSION_ID,'999') sid ,
      substr(LOCK_TYPE,1,30) Type,
      substr(lock_id1,1,45) Object_Name,
      substr(mode_held,1,4) HELD,
      substr(mode_requested,1,4) REQ,
      lock_id2 lock_addr
   FROM dba_lock_internal;


you may try following steps, too

// step 1

Identify sessions that are using PACKAGES using the query below.

The following query displays all objects currently being accessed by each session:

set lines 120
col username format a20
col owner    format a15
col object   format a30
col type     format a15

select a.sid, a.serial#, a.username, b.owner, b.object, b.type
  from
    v$session a,
    v$access  b
  where
    a.sid = b.sid
  and
    b.object like upper('%&object%');

// step2

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';