R12 查询EBS用户相关SQL
--R12查询EBS在线用户SQL
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;
