查询EBS在线用户SQL(R12)

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;
备注:是从DBA大哥那获取到的,又加工了一下,是山寨版的原创!

 

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 = <...>
 ORDER BY b.piece

 

 

查询某用户一年登录EBS失败的次数：
用 apps/apps_password 登录：

SQL>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>';

另外如果查询登录成功的次数， 可以查询表dns_logins