OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » How To Get APEX users who are currently active

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » How To Get APEX users who are currently active (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Get APEX users who are currently active [message #687460]

Fri, 10 March 2023 15:04

wtolentino
Messages: 421
Registered: March 2005

Senior Member

To get the workspaces I would query the dba_user views

USERNAME                     USER_ID
------------------------- ----------
APEX_030200                      190
APEX_LISTENER                    262
APEX_PUBLIC_USER                 170
APEXSYSUSER                      280
APEX_REST_PUBLIC_USER            263
APEX_040100                      216
APEX_LISTENER_USER               217
APEX_050000                      223
APEX_210100                      279

Then with that result I would use the result and to the wwv_flow_fnd_user

   USER_ID USER_NAME
---------- ----------------------------------------
0015941536 APEXME
9454048894 USER1
8043592063 USER2

How do I link this to the v$session view so I can get the info about who is currently have active session?

Please advise. Thank you.

Report message to a moderator

Re: How To Get APEX users who are currently active [message #687478 is a reply to message #687460]

Tue, 14 March 2023 08:26

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

What are you trying to determine? It is the view APEX_WORKSPACE_SESSIONS that will show the sessions that exist. Though many of them will be sessions where the user has actually disconnected without bothering to log out: they get tidied up by the ORACLE_APEX_PURGE_SESSIONS job that, IIRC, runs every hour.

Report message to a moderator

Re: How To Get APEX users who are currently active [message #687480 is a reply to message #687478]

Wed, 15 March 2023 08:54

wtolentino
Messages: 421
Registered: March 2005

Senior Member

We are trying to find out who owns/runs a process that is taking long to complete. I usually use this query based on the v$session view.

select vs1.username, vs1.sid, vs1.serial#, 
       vs1.osuser, vs1.machine, vs1.logon_time,
       (vs1.last_call_et / 60) mins_running, 
       vs2.sql_text, vs1.last_call_et
  from v$session vs1 join v$sqltext_with_newlines vs2
       on vs1.sql_address = vs2.address
 where vs1.status = 'ACTIVE'
   and vs1.type not in ('BACKGROUND','SYSTEM')
   and vs1.last_call_et > 60
order by vs1.sid, vs1. serial#, vs2.piece;

However, it does not actually gives the info about who are the APEX users who run query. I am attempting to find info how to link the APEX tables/views to the v$sessions. Or there might be some tables/views on the APEX that I could use to get that info.

Thanks.

[Updated on: Wed, 15 March 2023 08:56]

Report message to a moderator

Re: How To Get APEX users who are currently active [message #687481 is a reply to message #687480]

Wed, 15 March 2023 09:00

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Your query will show you only apex_public_user for APEX sessions, you need to include v$session.client_identifier

Report message to a moderator

Re: How To Get APEX users who are currently active [message #687489 is a reply to message #687480]

Thu, 16 March 2023 02:31

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Also. rather than

where vs1.status = 'ACTIVE'

I would use

where wait_class <> 'Idle'

Report message to a moderator

Re: How To Get APEX users who are currently active [message #687511 is a reply to message #687489]

Thu, 23 March 2023 10:15

wtolentino
Messages: 421
Registered: March 2005

Senior Member

"where wait_class <> 'Idle'"

Yes that will be very helpful. Thanks.

Report message to a moderator

Previous Topic:	ORDS 22.2.1.r2021302 configuration for adding other database
Next Topic:	migrate APEX from Oracle 11g to Oracle XE 21

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 01:45:17 CST 2024