Commonly used Scripps for all the Oracle applications Database Administrators/Sysadmin:
Canceling Concurrent program from backend:
1) Get the SID for the given request ID using below script
select
req.request_id
, req.oracle_process_id
, req.os_process_id
, ses.sid
, ses.serial#
, ses.program
, req.logfile_name log
, req.outfile_name out
from v$process pid
, v$session ses
, apps.FND_CONCURRENT_REQUESTS req
where req.request_id = &request_id
and req.oracle_process_id = pid.spid(+)
and pid.addr = ses.paddr(+)
2) Take the SID and cancel the program using below command
Alter system kill session ‘sid, serial#’
3) If it is a request set and you can not able to cancel from front-end then cancel the program using below command:
Update apps.fnd_concurrent_requests
Set phase_code='C',status_code='D' where request_id='12345678'
4) Even after canceling the SID still it is having Oracle process id then take the Oracle Process id from the script 1 and go to UNIX session and connect as applmgr and kill that particular session
Kill -9 Oracle_process_id
Script to find out lock for any session:
select
substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
l.process,
s.sid,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status != 'KILLED';
Script to find out all the running sessions
select a.request_id "rqst_id"
,a.program_application_id "pgm_appl_id"
,a.concurrent_program_id "conc_pgm_id"
,a.status_code||decode(a.resubmit_interval, null, null, '*') "stat_cd"
,b.os_process_id "os_pid"
,v.spid "vspid"
,v.sid "vsid"
,v.serial# "vserial#"
,a.priority "pri"
,substr(d.concurrent_queue_name,1,3) "que"
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "time"
,round(f.time_avg,2) "avgtime"
,decode(sign(a.requested_start_date - a.request_date),1,
round(((sysdate-requested_start_date) * 1440),0),
round(((sysdate-request_date) * 1440),0)) "walltime"
,e.user_name "usr"
,c.concurrent_program_name||' - '||rtrim(ct.user_concurrent_program_name) program"
from APPLSYS.fnd_concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ct
,APPLSYS.fnd_concurrent_queues d
,APPLSYS.fnd_user e
,APPLSYS.fcr_stats f
,(select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr) v
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.concurrent_program_id = f.concurrent_program_id(+)
and a.program_application_id = f.application_id(+)
and a.program_application_id = c.application_id
and a.concurrent_program_id = ct.concurrent_program_id
and a.program_application_id = ct.application_id
and b.concurrent_queue_id = d.concurrent_queue_id
and ct.language = 'US'
and a.requested_by = e.user_id
and a.phase_code in ('R','T')
and a.oracle_process_id=v.spid(+)
order by 11 asc
Script to find out the concurrent manager for a given request
Select /*+ RULE */
decode(F.node_name,'hp14fat', 'hp14fat:rptd *', 'hp05fat', 'hp05fat:prod', '' ) Node,
substr(User_Concurrent_Queue_Name,1,20) Manager,
Request_Id Request, User_name,
Fpro.OS_PROCESS_ID OSproc,
substr(user_Concurrent_Program_Name,1,30) Program,
To_Char(Actual_Start_Date, 'RR-MM-DD HH24:MI') Started
from apps.Fnd_Concurrent_Queues_tl Fcq, apps.Fnd_Concurrent_Requests Fcr,
apps.Fnd_Concurrent_Programs_tl Fcp,
apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes Fpro,
apps.fnd_concurrent_queues f
where
Phase_Code = 'R' And
Status_Code <> 'W' And
Fcr.Controlling_Manager = Concurrent_Process_Id And
(Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id And
Fcq.Application_Id = Fpro.Queue_Application_Id ) And
(Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
Fcr.Program_Application_Id = Fcp.Application_Id ) And
F.concurrent_queue_id = Fcq.concurrent_queue_id And
Fcr.Requested_By = User_Id
order by Started,2
Script to find locks (wait)
set linesize 150
set pagesize 1000
set feedback off
set verify off
col sidstatus form a7 head "Status"
column username format a8
column osuser format a8
column machine format a12
column terminal format a8
column sid format 99999
column serial# format 999999
column logon_time format a14
column program form a52
rem WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
prompt
prompt
prompt Waiting vs Holding ...
prompt ======================
col LOCK_TYPE form a12
col MODE_HELD form a20
col MODE_REQUESTED form a20
col LOCK_ID1 form a10
col LOCK_ID2 form a10
REM SID output in special format for URL handling
col waiting_sid format a11 head "Waiting"
col holding_sid format a11 head "Holding"
Select
':SID:' || WAITING_SESSION || ':' waiting_sid,
':SID:' || HOLDING_SESSION || ':' holding_sid,
LOCK_TYPE, MODE_HELD, MODE_REQUESTED, LOCK_ID1, LOCK_ID2
From SYS.DBA_WAITERS
;
set termout off
define hsid1 = 0
define hsid2 = 0
define hsid3 = 0
define hsid4 = 0
col hsid1 new_val hsid1
col hsid2 new_val hsid2
col hsid3 new_val hsid3
col hsid4 new_val hsid4
select 0 hsid1, 0 hsid2, 0 hsid3, 0 hsid4 from dual;
REM Below - get maximum of 4 holding sessions ids
REM Select max(decode(mod(ROWNUM,5),1,HOLDING_SESSION,0)) hsid1,
REM max(decode(mod(ROWNUM,5),2,HOLDING_SESSION,0)) hsid2,
REM max(decode(mod(ROWNUM,5),3,HOLDING_SESSION,0)) hsid3,
REM max(decode(mod(ROWNUM,5),4,HOLDING_SESSION,0)) hsid4
REM From SYS.DBA_WAITERS
REM Where ROWNUM < 5
REM Group by ROWNUM - mod(ROWNUM,5)
REM ;
Select max(decode(mod(rownum,5),1,SID,0)) hsid1,
max(decode(mod(rownum,5),2,SID,0)) hsid2,
max(decode(mod(rownum,5),3,SID,0)) hsid3,
max(decode(mod(rownum,5),4,SID,0)) hsid4
From v$lock
Where block != 0
And rownum < 5
Group by rownum - mod(rownum,5)
;
variable hsid1 number;
variable hsid2 number;
variable hsid3 number;
variable hsid4 number;
begin
:hsid1 := &hsid1;
:hsid2 := &hsid2;
:hsid3 := &hsid3;
:hsid4 := &hsid4;
end;
/
set termout on
prompt
prompt
prompt Waiting ...
prompt ===========
Select decode(S.LOCKWAIT, NULL, 'Holding', 'Waiting') sidstatus,
S.SID, S.SERIAL#,
S.PROCESS, S.USERNAME, S.OSUSER,
to_char(S.LOGON_TIME, 'mm/dd hh24:mi:ss') "LOGON_TIME",
S.MACHINE, S.TERMINAL, S.PROGRAM
From V$SESSION S
Where S.LOCKWAIT IS NOT NULL
;
prompt
prompt
prompt Holding ...
prompt ===========
Select decode(S.LOCKWAIT, NULL, 'Holding', 'Waiting') sidstatus,
S.SID, S.SERIAL#,
S.PROCESS, S.USERNAME, S.OSUSER,
to_char(S.LOGON_TIME, 'mm/dd hh24:mi:ss') "LOGON_TIME",
S.MACHINE, S.TERMINAL, S.PROGRAM
From V$SESSION S
Where S.SID in (:hsid1, :hsid2, :hsid3, :hsid4)
;
clear breaks
prompt
prompt
prompt Session Wait Info
prompt =================
col EVENT form a40
col P1TEXT form a30
col Minutes_in_Wait form 999999
Select decode(S.LOCKWAIT, NULL, 'Holding', 'Waiting') sidstatus,
W.SID, SECONDS_IN_WAIT/60 "Minutes_in_Wait", STATE,
EVENT, P1TEXT
From V$SESSION_WAIT W, V$SESSION S
Where (S.LOCKWAIT IS NOT NULL Or S.SID in (:hsid1, :hsid2, :hsid3, :hsid4))
And W.SID = S.SID
Script to find out the users having System Administrator responsibility:
select a.responsibility_name, c.user_name, c.description
from apps.fnd_responsibility_vl a,apps.fnd_user_resp_groups b, apps.fnd_user c
where a.responsibility_id=b.responsibility_id and
a.application_id=b.responsibility_application_id and
b.user_id=c.user_id and
a.responsibility_id=20420 and
(b.end_date IS NULL or b.end_date > SYSDATE) and
(c.end_date IS NULL or c.end_date > SYSDATE)
Compiling Invalid objects using below script:
DECLARE
obj_number number := 0;
cursor C1 is select o.obj#,
'ALTER ' || decode (o.type#,
4, 'VIEW ',
7, 'PROCEDURE ',
8, 'FUNCTION ',
9, 'PACKAGE ',
11, 'PACKAGE ',
12, 'TRIGGER ',
13, 'TYPE ',
14, 'TYPE ',
' ') ||
u.name || '.' || o.name || ' COMPILE ' ||
decode (o.type#,
9, 'SPECIFICATION',
11, 'BODY',
13, 'SPECIFICATION',
14, 'BODY',
' ')
from obj$ o, user$ u
where o.obj# > obj_number and
u.user# = o.owner# and o.remoteowner is NULL and
o.status in (4,5,6) and o.type# in (4, 7, 8, 9, 11, 12, 13, 14)
order by o.obj#;
DDL_CURSOR integer;
ddl_statement varchar2(200);
iterations number;
loop_count number;
my_err number;
validate number;
BEGIN
loop_count := 0;
-- To make sure we eventually stop, pick a max number of iterations
select count(*) into iterations from obj$ where remoteowner is NULL and
status in (4,5,6) and type# in (4, 7, 8, 9, 11, 12, 13, 14);
DDL_CURSOR := dbms_sql.open_cursor;
OPEN C1;
LOOP
BEGIN
FETCH C1 INTO obj_number, ddl_statement;
EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
EXCEPTION
WHEN OTHERS THEN
my_err := SQLCODE;
IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
CLOSE C1;
-- Here is why C1 orders by obj#. When we restart the query, we
-- will only find object with obj# greater than the last one tried.
-- This keeps us from re-trying objects that failed.
OPEN C1;
GOTO continue;
ELSE
RAISE;
END IF;
END;
-- Check to see if already validated as a result of earlier compiles
select count(*) into validate from obj$ where obj# = obj_number and
status in (4,5,6);
IF validate = 1 THEN
BEGIN
-- Issue the Alter Statement (Parse implicitly executes DDLs)
dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
EXCEPTION
WHEN OTHERS THEN
null; -- ignore, and proceed.
END;
END IF;
<<continue>>
loop_count := loop_count + 1;
END LOOP;
dbms_sql.close_cursor(DDL_CURSOR);
CLOSE C1;
END;
/
