List of users connect to PeopleSoft script

 1. To Find the List of Users Log in PIA

SELECT COUNT(nr) users_number FROM (SELECT COUNT(oprid) AS nr FROM psaccesslog WHERE To_date(SYSDATE) - To_date(logindttm) >= 0 AND To_date(logoutdttm) - To_date(SYSDATE) >= 0 GROUP  BY oprid);

################################################################################

2. The following query will list all the PIA users through PSACCESSLOG record:

 SELECT DISTINCT oprid,

                logipaddress,

                To_char(logindttm, 'YYYY-MM-DD:hh:mi:ss')   logintime,

                To_char(logoutdttm, 'YYYY-MM-DD:hh:mi:ss')  logoutime,

                To_char(( SYSDATE ), 'YYYY-MM-DD:hh:mi:ss') currtime

FROM   psaccesslog

WHERE  To_date(SYSDATE) - To_date(logindttm) >= 0

       AND To_date(logoutdttm) - To_date(SYSDATE) >= 0;  

   

#######################################################################################

   

3. The following query will list all the users in the current database(not just those logged in from PIA): SELECT sid,

       serial#,

       username,

       terminal,

       program

FROM   v$session

WHERE  username IS NOT NULL;