Sometimes when we try to compile or create packages it may hang ,
Generally package compilation or creating doesn't take much time in the oracle database even if there is an issue with PL/SQL code .
So we need to find the cause when Compiling Oracle Packages takes a lot of Time.
Basically it happens due to locking sessions so we need to identify the locking sessions whatever using the same package and kill it .
Below are the scripts to find the locking session details .
Script 1:
select * from dba_ddl_locks where name like '%PACKAGE NAME%';
Script 2 :
SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1,
FROM dba_lock_internal l,
v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE '%PACKAGE NAME%'
AND l.lock_type ='Body Definition Lock';
Script 3:
Get the session details which are blocking package definition.
COL sid FOR 999999
COL lock_type FOR A38
COL mode_held FOR A12
COL mode_requested FOR A12
COL lock_id1 FOR A20
COL lock_id2 FOR A20
COL kill_sid FOR A50
SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1,
'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM dba_lock_internal l,
v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE '%&package_name%'
AND l.lock_type = 'Body Definition Lock'
/
Script 4:
Check out what the offending session is doing .
SELECT s.sid,
NVL(s.username, 'ORACLE PROC') username,
s.osuser,
p.spid os_pid,
s.program,
t.sql_text
FROM v$session s,
v$sqltext t,
v$process p
WHERE s.sql_hash_value = t.hash_value
AND s.paddr = p.addr
AND s.sid = &session_id
AND t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/
Script 5:
You can get more info about the offending sessions by using below SID and PID script.
if those sessions are related to concurrent requests ,you can find the info from the scripts below .
Get the current running and previous runs history and take the necessary action .
You can Get the concurrent request ID and Name by using below scirpt .
set echo off pages 100 lines 202
column REQUEST heading 'Request' format a9
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(e)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,9)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,
substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,
s.sid,s.serial#
--substr(fcr.os_process_id,1,7)OS_PIDa
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,
v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
Script 6:
To know the history of the Requests /Previous Runs :
SELECT a.request_id,b.user_concurrent_program_name,to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS'),
to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS'),
decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code),
decode(a.status_code,'E','Error', 'C','Normal',
'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning',
'R','Normal', 'W', 'Paused', a.status_code),
a.ARGUMENT_TEXT FROM apps.fnd_concurrent_programs_vl b,apps.fnd_concurrent_requests a
WHERE a.concurrent_program_id = b.concurrent_program_id and
b.user_concurrent_program_name like
'%&CONCURRENT_PROGRAM%' order by 3;
Script 7:
Run the kill session command (kill_cmd) generated from the script 3 output in the first script if required .
alter system kill session '218,12455' immediate;
