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