From:http://hutianci.iteye.com/blog/934921
SELECT U.USER_NAME,
APP.APPLICATION_SHORT_NAME,
FAT.APPLICATION_NAME,
FR.RESPONSIBILITY_KEY,
FRT.RESPONSIBILITY_NAME,
FFF.FUNCTION_NAME,
FFT.USER_FUNCTION_NAME,
ICX.FUNCTION_TYPE,
ICX.FIRST_CONNECT,
ICX.LAST_CONNECT
FROM ICX_SESSIONS ICX,
FND_USER U,
FND_APPLICATION APP,
FND_APPLICATION_TL FAT,
FND_RESPONSIBILITY FR,
FND_RESPONSIBILITY_TL FRT,
FND_FORM_FUNCTIONS FFF,
FND_FORM_FUNCTIONS_TL FFT
WHERE 1 = 1
AND U.USER_ID = ICX.USER_ID
AND ICX.RESPONSIBILITY_APPLICATION_ID = APP.APPLICATION_ID
AND FAT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND FAT.LANGUAGE = 'ZHS'
AND FR.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND FR.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
AND FRT.LANGUAGE = 'ZHS'
AND FRT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND FRT.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
AND FFF.FUNCTION_ID = ICX.FUNCTION_ID
AND FFT.FUNCTION_ID = ICX.FUNCTION_ID
AND ICX.DISABLED_FLAG != 'Y'
AND ICX.PSEUDO_FLAG = 'N'
AND (ICX.LAST_CONNECT +
DECODE(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
NULL,
ICX.LIMIT_TIME,
0,
ICX.LIMIT_TIME,
FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >
SYSDATE
AND ICX.COUNTER < ICX.LIMIT_CONNECTS;
SELECT FND.USER_NAME,
ICX.RESPONSIBILITY_APPLICATION_ID,
ICX.RESPONSIBILITY_ID,
FRT.RESPONSIBILITY_NAME,
ICX.SESSION_ID,
ICX.FIRST_CONNECT,
ICX.LAST_CONNECT,
DECODE((ICX.DISABLED_FLAG), 'N', 'ACTIVE', 'Y', 'INACTIVE') STATUS
FROM FND_USER FND, ICX_SESSIONS ICX, FND_RESPONSIBILITY_TL FRT
WHERE FND.USER_ID = ICX.USER_ID
AND ICX.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
AND ICX.DISABLED_FLAG <> 'Y'
AND TRUNC(ICX.LAST_CONNECT) = TRUNC(SYSDATE)
ORDER BY ICX.LAST_CONNECT;
--监控concurrent 正在执行的sql
SELECT a.sid, a.serial#, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
AND a.sid = :p_sid
ORDER BY b.piece
--查询某用户一年登录EBS失败的次数:
--用 apps/apps_password 登录:
select count(login_name)
from fnd_unsuccessful_logins l, fnd_user u
where l.user_id = u.customer_id
and attempt_time > (select max(start_time)
from fnd_logins l
where l.user_id = u.user_id)
and (attempt_time + 265) > SYSDATE
and u.user_name = '<username>';
--查询用户登录次数
select peo.person_id,peo.full_name,fu.user_id,fu.user_name,
hl.location_code,hl.description,count(fl.user_id) as login_times
from fnd_user fu,
per_all_people_f peo,
per_all_assignments_f ass,
hr_locations hl,
fnd_logins fl
where fu.employee_id = peo.person_id
and ass.person_id = peo.person_id
and ass.location_id = hl.location_id
and fl.user_id(+) = fu.user_id
and fu.user_name = :user_name
group by peo.person_id,peo.full_name,fu.user_id,fu.user_name,
hl.location_code,hl.description;