Oracle eBusiness suite - SQL to list all users currently logged in

A database session will be created for every user logging in to Oracle eBusiness suite. The details of the session can be extracted from v$session view. The below SQL extracts useful information from v$session.

WITH sessions_inline AS

 (SELECT client_identifier user_name

        ,(SELECT responsibility_name

          FROM   fnd_responsibility_vl

          WHERE  responsibility_key = SUBSTR(action

                                            ,INSTR(action

                                                  ,'/'

                                                  ,1

                                                  ,1) + 1)) responsibility

        ,DECODE(SUBSTR(module

                      ,INSTR(module

                            ,':'

                            ,1

                            ,2) + 1

                      ,INSTR(module

                            ,':'

                            ,1

                            ,3) - INSTR(module

                                       ,':'

                                       ,1

                                       ,2) - 1)

               ,'frm'

               ,'Forms Screen'

               ,'wf'

               ,'Workflow'

               ,'cp'

               ,'Concurrent Process'

               ,'fwk'

               ,'Self Service Page'

               ,'bes'

               ,'Business Event'

               ,'gsm'

               ,'Workflow') usage_type

        ,SUBSTR(module

               ,INSTR(module

                     ,':'

                     ,1

                     ,3) + 1) ebs_module

        ,

        

         ses.*

  FROM   v$session ses

  WHERE  client_identifier IS NOT NULL)

SELECT user_name

      ,responsibility

      ,usage_type

      ,ebs_module

      ,DECODE(usage_type

             ,'Forms Screen'

             ,DECODE(ebs_module

                    ,'FNDSCSGN'

                    ,'EBS Navigator'

                    ,(SELECT user_form_name FROM fnd_form_vl WHERE form_name = ebs_module))) forms_screen

      ,DECODE(usage_type

             ,'Concurrent Process'

             ,(SELECT user_concurrent_program_name FROM fnd_concurrent_programs_vl WHERE concurrent_program_name = ebs_module)) concurrent_program

      ,DECODE(usage_type

             ,'Concurrent Process'

             ,(SELECT user_concurrent_queue_name FROM fnd_concurrent_queues_vl WHERE concurrent_queue_name = ebs_module)) concurrent_manager

FROM   sessions_inline ses

ORDER  BY ses.usage_type;

Please note that most of the non-EBS sessions(Toad, JDeveloper etc connecting to database directly) will be omitted from this report.