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)
How To Get APEX users who are currently active [message #687460] Fri, 10 March 2023 15:04 Go to next message
wtolentino
Messages: 390
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.
Re: How To Get APEX users who are currently active [message #687478 is a reply to message #687460] Tue, 14 March 2023 08:26 Go to previous messageGo to next message
John Watson
Messages: 8916
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.
Re: How To Get APEX users who are currently active [message #687480 is a reply to message #687478] Wed, 15 March 2023 08:54 Go to previous messageGo to next message
wtolentino
Messages: 390
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 Go to previous messageGo to next message
John Watson
Messages: 8916
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
Re: How To Get APEX users who are currently active [message #687489 is a reply to message #687480] Thu, 16 March 2023 02:31 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
Also. rather than

where vs1.status = 'ACTIVE'

I would use

where wait_class <> 'Idle'
Re: How To Get APEX users who are currently active [message #687511 is a reply to message #687489] Thu, 23 March 2023 10:15 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
"where wait_class <> 'Idle'"

Yes that will be very helpful. Thanks.
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:
  


Current Time: Tue Feb 27 21:27:12 CST 2024