Miscellaneous Scripts - Oracle EBS

### Running Requests
SELECT b.request_id, a.user_concurrent_program_name, b.cancel_or_hold,b.argument_text,
TO_CHAR (b.actual_start_date, 'MM/DD/YY HH24:MI:SS') starttime,
TO_CHAR (b.actual_completion_date, 'MM/DD/YY HH24:MI:SS') endtime,
ROUND ((b.actual_completion_date - b.actual_start_date) * (60 * 24),2) runtime,
b.outcome_code, b.completion_text
FROM fnd_concurrent_programs_tl a, fnd_concurrent_requests b
WHERE a.concurrent_program_id = b.concurrent_program_id
AND b.actual_start_date > SYSDATE - 3
and b.actual_completion_date is null
AND a.LANGUAGE ='US';


### Request Last Run
select fcr.REQUEST_ID, fcp.USER_CONCURRENT_PROGRAM_NAME,fcr.REQUEST_DATE,
fcr.argument_text,fcr.status_code "status",fcr.phase_code "phase",
to_char(fcr.ACTUAL_START_DATE, 'DD-MON-YY HH24:MI:SS') "ACTUAL_START_DATE",
to_char(fcr.ACTUAL_COMPLETION_DATE, 'DD-MON-YY HH24:MI:SS') "ACTUAL_COMPLETION_DATE",
ROUND ((fcr.actual_completion_date - fcr.actual_start_date) * (60 * 24),2) runtime
from fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp
where
fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND fcp.USER_CONCURRENT_PROGRAM_NAME Like '%Gather%'
and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate order by REQUESTED_START_DATE;


### Session details of running requests

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,

s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id;


### Client tools connected session
select -- 'alter system disconnect session ''' ||sid|| ',' ||serial#||'''  immediate;',
SID, SERIAL#, STATUS, SERVER, OSUSER, MACHINE, PROGRAM, SQL_ID, MODULE, LOGON_TIME, LAST_CALL_ET/3600
from v$session where
osuser NOT IN ('&osuser')
order by logon_time ASC;


### Object Lock session details
select --'alter system kill session ''' ||v.sid|| ',' || v.serial#||''' immediate;',
--'alter system kill session ''' ||v.sid|| ',' || v.serial#|| ',@' ||v.inst_id|| ''' immediate;'
,do.object_name,do.object_type,v.sid,v.serial#,v.program
,v.machine,v.osuser ,v.client_identifier,v.service_name
,TO_CHAR (v.logon_time, 'MM/DD/YY HH24:MI:SS') starttime
,dl.lock_type
from dba_locks dl,dba_objects do,gv$session v where
do.object_name like 'XX%' and
--v.client_identifier like '%%' and
do.object_type='TABLE'
and dl.lock_id1 = do.object_id
and v.sid = dl.session_id;


### Find Details of SPID
select  --'alter system kill session ''' ||s.sid|| ',' || s.serial#|| ',@' ||s.inst_id|| ''' immediate;',
s.INST_ID, s.SID, s.SERIAL#, s.CLIENT_IDENTIFIER,TO_CHAR (s.logon_time, 'MM/DD/YY HH24:MI:SS') starttime,s.MODULE,s.sql_id ,Sa.Sql_Fulltext
from gv$session s, gv$process pr,v$sqlarea sa where s.paddr = pr.addr
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and pr.spid=18811;

### Find Session Lock
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;


### Gather Stats from back-end
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 3,'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');
exec FND_STATS.gather_table_stats('ONT','OE_ORDER_LINES_ALL',PERCENT => 100,DEGREE => 4 );
exec fnd_stats.gather_schema_statistics('ONT',PERCENT => 10,DEGREE => 4);


### Current Running SQLs
select s.SID,s.SERIAL#, s.STATUS, s.SERVER, s.OSUSER, sm.SQL_ID, s.MODULE, s.LOGON_TIME,s.MACHINE,s.PROGRAM, round(sm.elapsed_time/60000000,2) elapsed_time, sm.sql_text, sm.cpu_time, sm.buffer_gets, sm.disk_reads
from v$sql_monitor sm, v$session s
where sm.SID=s.SID
and sm.status = 'EXECUTING';


### Recently Changed Profile Options
select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;


### Check any debug enabled in profile
select  distinct
        a.application_short_name app_short,
        user_profile_option_name optname,
        decode(level_id,
        10001,'SITE',
        10002,'APP : '||a2.application_short_name,
        10003,'RESP: '||r.responsibility_key,
        10004,'USER: '||u.user_name,
        'Unknown') d_level,
        profile_option_value optval,
        v.last_update_date updated
from fnd_profile_options_vl o,
        fnd_profile_option_values v,
        fnd_application a,
        fnd_application a2,
        fnd_responsibility r,
        fnd_user u
where (
        upper(o.user_profile_option_name) like '%DEBUG%' or
        upper(o.user_profile_option_name) like '%TRACE%'
  or upper(o.user_profile_option_name) like '%LOGG%'
        )
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;


### Check any debug enabled in concurrent program
SELECT concurrent_program_name prog_name
     , enable_trace
     , last_update_date
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y'
/

### Hold/UnHold the concurrent Requests
SELECT fcr.request_id,fcp.USER_CONCURRENT_PROGRAM_NAME,fcr.phase_code,fcr.status_code,
fcr.HOLD_FLAG,fcr.RESUBMIT_INTERVAL,fcr.RESUBMIT_INTERVAL_UNIT_CODE,fcr.ARGUMENT_TEXT
FROM fnd_concurrent_programs_tl fcp, fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id 
and fcr.phase_code = 'P' and fcr.status_code IN ('I','Q')
and fcr.HOLD_FLAG='N';

-- To Hold the Scheduled Requests
update fnd_concurrent_requests set HOLD_FLAG='Y' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='N';

-- To REMOVE HOLD on Requests
update fnd_concurrent_requests set HOLD_FLAG='N' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='Y';

-- To cancel the pending/scheduled requests
update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='N';


### TEMP Usage Monitor
SELECT * FROM   dba_temp_free_space;


### Number of user sessions
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time,
0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < limit_connects;


### To find how many users connected in EBS
SELECT DISTINCT icx.session_id, icx.user_id, fu.user_name
,TO_CHAR (icx.creation_date, 'MM/DD/YY HH24:MI:SS') creation_date,
TO_CHAR (Icx.Last_connect, 'MM/DD/YY HH24:MI:SS') Last_connect,fu.description
FROM icx_sessions icx, fnd_user fu
WHERE disabled_flag != 'Y'
AND icx.pseudo_flag = 'N'
AND ( last_connect+ DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),NULL, limit_time,0, limit_time,
fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') / 60)/ 24) > SYSDATE
AND icx.counter < limit_connects
AND icx.user_id = fu.user_id;

### To Find Workflow Mailer Status and Log file name
select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','mailer container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;