Debug or Trace Option Enabled in Profiles
SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
Run this script to see if any Debug or Trace profile options have been set to Y, meaning that they are enabled. Some profile options may be required to be set to Y, but others should be N.
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 (
o.user_profile_option_name like '%Debug%' or
o.user_profile_option_name like '%DEBUG%' or
o.user_profile_option_name like '%Trace%' or
o.user_profile_option_name like '%TRACE%'
)
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;
http://oracle-apps-dba.blogspot.com/2007/07/apps-user-connection-details.html
And here is Modified version that shows and requests as well others session details.
Remark:
it use input parameter to reduce result on only named apps user
2) It should be run as apps user or use "set current_schema" part in front.
set show off
SET VER OFF
set head on;
set timing on;
undefine apps_user;
accept apps_user char prompt 'Input starting letters of APPS username (Enter for all...): ';
set linesize 300;
set pagesize 200;
col sid_serial for a13;
col user_name for A10;
col module for a22;
col Responsibility for a29;
col function for a30;
col F_Type for a10;
col ap_pid for a6;
col db_pid for a6;
break on USER_NAME on db_pid on ap_pid on sid_serial
select * from (
select
usr.user_name user_name
,v.spid db_pid
,ses.process ap_pid
,ses.sid||','||ses.serial# sid_serial
,ses.module
,rsp.responsibility_name Responsibility
,fuc.function_name Function
,i.function_type F_Type
,to_char(i.last_connect,'dd.mm hh24:mi') F_Start
from
apps.icx_sessions i
,apps.fnd_logins l
,apps.fnd_appl_sessions a
,apps.fnd_user usr
,apps.fnd_responsibility_tl rsp
,apps.fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from apps.fnd_login_responsibilities t1
where t1.login_id = l.login_id
)
and usr.user_name like '&apps_user%'
union
select
usr.user_name
,v.spid
,ses.process
,ses.sid||','||ses.serial# sid_serial
,ses.module
,rsp.responsibility_name
,null
,null
,null form_start_time
from
apps.fnd_logins l
,apps.fnd_login_responsibilities r
,apps.fnd_user usr
,apps.fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and r.audsid = ses.audsid
and usr.user_name like '&apps_user%'
union
select
usr.user_name
,v.spid
,ses.process
,ses.sid||','||ses.serial# sid_serial
,ses.module
,null
,frm.user_form_name
,ff.type
,to_char(f.start_time,'dd.mm hh24:mi')
from
apps.fnd_logins l
,apps.fnd_login_resp_forms f
,apps.fnd_user usr
,apps.fnd_form_tl frm
,apps.fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&apps_user%'
union
select
fu.user_name
,cr.oracle_process_id
,cr.OS_PROCESS_ID
,vs.SID || ',' || vs.serial# sid_serial
,(SELECT cp.concurrent_program_name
FROM APPS.fnd_concurrent_programs cp
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
)
,(select responsibility_name
from apps.fnd_responsibility_vl
where responsibility_id = cr.responsibility_id
and application_id = cr.responsibility_application_id
)
,(select execution_file_name
from apps.fnd_executables fe, apps.fnd_concurrent_programs fcp
where fcp.concurrent_program_id = cr.concurrent_program_id
and fcp.application_id = cr.program_application_id
and fe.executable_id = fcp.executable_id
and fe.application_id = fcp.executable_application_id
)
,'REQUEST'
,TO_CHAR(NVL(cr.actual_start_date, cr.requested_start_date), 'DD.MM HH24:MI') start_time
FROM
APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
apps.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
/*
(cr.phase_code < 'I' OR cr.phase_code > 'I')
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
*/
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
AND vs.sid is not null
and fu.user_name like '&apps_user%'
)
order by user_name, db_pid, ap_pid, sid_serial
;
