Cannot compile procedures while they are been used
                                                 Last update (2014-04-24 02:52:10)
                                                                                                                   Date added (2008-02-13 19:10:11)

Summary
You can't grant execute privileges to procedures that are currently been used by a session.
The same stands and for compiling a procedure/function/package while it is been used by a session.

If you try it, then it is more likely you will wait forever or you will get the error
ORA-04021: timeout occurred while waiting to lock object.

So in that case you need to know if the object is been used and from whom.
Find the session using the procedure or package and kill the session.
After that you can grant execute privileges on object, or compile the procedure,function,package.
SELECT /*+ RULE */ a.SID, a.serial#, a.status, a.username, a.osuser, b.owner,a.program, b.OBJECT, b.TYPE,
'alter system kill session ' || ''''||a.SID || ', ' || a.serial# || '''' || ' immediate;' sql_kill
FROM v$session a, v$access b
WHERE a.SID = b.SID
AND b.OBJECT = 'COLLECTANDSEND'
AND owner = 'MONITOR'
AND b.TYPE = 'PROCEDURE'
--AND status = 'ACTIVE';
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search