May I ask what is the specific reason to be able to trace APEX session from SQL*Plus?
I am asking because, as you already know, APEX documentation suggest that appending &p_trace=YES to the URL is the way to trace apex session.
Since APEX is a web-based application, it uses state-less communication while interacting with the database, which means the ideal usage of a typical
APEX page should be "grab a DB connection; do the work QUICKLY; release the DB connection". In this manner, there is not much scope for tracing
the sessions from "outside".
I am not sure if it will work but from SQL*Plus, can you enable trace and then execute APEX page process by issuing a "SELECT httpuritype('<APEX URL>') from dual" ?
Thx for your reply.
>May I ask what is the specific reason to be able to trace APEX session from SQL*Plus?
>In this manner, there is not much scope for tracing the sessions from "outside".
If I could place code in PL/SQL then I could place that code on before (or many other "before") events in Apex and do with that data what) want without asking end user "Please could you place &p_trace=YES through phone and expect 101 errors and mistakes in that kind of starting trace).
Some actions (because of pretty strong security on production) only some users are allowed to run (execute) and problem is somewhere in Apex-server-client path, so need to be determined properly.
Trace according your suggestion is not possible in mine (and probably in many custom authenticated Apex) application.
What come to mine mind is a way to mark some context variables and then recognize them somehow in some database trigger (login or so)
If the main requirement is to be able to trace user sessions without manually asking user to change the URL, then would the URL Rewrite not help? If you are using an Apache HTTP server, a change to http.conf (to append the &p_trace=YES) will "enable" trace for all APEX pages. Might turn out to be a "sledgehammer" but maybe worth a try....
I guess you yourselves have written about it ?? https://www.google.co.uk/url?q=http://damir-vadas.blogspot.com/2012/09/oracle-http-server-modrewrite-to.html&sa=U&ei=VihaU-X4FO707AauzoHYDQ&ved=0CB4QFjAA&usg=AFQjCNFMVKUvfadfjXJeex1OarTktyP4Sg
Yes, this is me.
But this forum is for Oracle REST Data Services (former Apex Listener) and mine blog is explaining something completely different, if I understand mine post and this one question here.
When I remember how tracing is supported in Oracle EBS (out of the box, through ANY form link!!) I am really surprised with lack of interest in same subject here.
Apologies for misleading you as I was not aware of apex listener being rebranded as Oracle REST Data Services.
I am not sure if there can be another approach to your original problem but I am thinking if Oracle REST Data Services
manages to support for URL rewrite in future, that could be one potential approach to your problem.
At present, I guess there is no better way to do the same without manual intervention.
Thx for your answer.
>I guess there is no better way to do the same without manual intervention
If you mean adding "&p_trace=YES" to URL than Ok, otherwise do not follow you.
Interesting how one simple and more then reasonable question (tracing) gave no respond from no one here.
It is even more interesting when we all know that on any production this parts (manual url trace) is not allowed and any performance problem on Oracle support starts with- please upload a trace file....
If you are not able to utilize the feature that APEX has (or it is disabled at the instance level) then you are back to raw database tracing tools. You may need to grant your user extra privileges to trace.
Oracle Base has a great writeup on the tracing topic.
If you are tracing one session from another session, you may want to look into the dbms_support package.
If this is a regular problem for you, Enkitec has a product that allows secure trace file data collection.
thx for your answer-appreciated.
>If you are tracing one session from another session, you may want to look into the dbms_support package.
With all the respect to your post I'm pretty familiar with Oracle tracing capabilities through mentioned Oracle package. But this is not a problem here.
Mine (and think everyone other) problem in production environment, with certain level of security, is how to recognize Apex session in browser through Oracle SID, serial# in database.
Enkitec is really great company with dozen of very interesting plugins and other stuff in Apex/Oracle are-I respect them a lot. But in this case I do not need anything from them but to trace Apex session, which I do not see as public (free) part of their offer-what I find trace capability as should be.
Your question has changed.
Your question now: "How do I identify a session in the database that belongs to an APEX page?"
Use the session tables to match your APEX connection to the information in the gv$session table.
Yes - this is a "free" information in the database, but it is also secure. You will need elevated privileges to do this. Use with caution.
You will not find a PLUG-IN that can do tracing if the base user can not do tracing. The reason the URL feature is there from APEX is because APEX (the framework) has the access and ability - but that is not passed down to each workspace / schema without using the framework.
I have no details of your installation or your application, so you will need to determine what types of calls are being made to the database and with what method (EPG, OHS, ORDS, etc..)
select SID, SERIAL#,inst_id,program,module,event,Action,machine, client_info, CLIENT_IDENTIFIER
from gv$session s where type!='BACKGROUND'
and status='ACTIVE' and sql_id is not null;
The CLIENT_IDENTIFIER will show you SESSION and USER
The MODULE will show you APP and PAGE
From there, you can do all your SID related tracing you need.