Unable to Compile or Drop PL/SQL Package or Procedure

If you are unable to compile any plsql Package or Procedure in Database then there is a possibility that the package or procedure is being used by some Program or some SQL statement.

Below are the steps to find out the SQL which is blocking you from compiling or Dropping your object.

1. Check the Object being accessed by :-

SELECT * FROM v$access WHERE object = '<OBJ_NAME>';

OR

SELECT * FROM gv$access WHERE object = '<OBJ_NAME>';

2. Check the Session which is locking the object by passing SID from first query :-

select * from v$session where sid = <sid>;

OR

select * from gv$session where sid = 3305;

3. Check the sql id being executed at the time :-

select *from  v$sql where sql_id = '<sql_id>' ;

OR

select *from  gv$sql where sql_id = '<sql_id>' ;