12 Replies Latest reply: Jun 5, 2014 8:40 AM by Tim St. H. RSS

    Trace Apex session from sqlplus

    Damir Vadas2

      Hi,

      Apex 4.2.4, Oracle 11.2.0.3, Apex Listener 2.0.5

      How to start Apex session trace from sqlplus command line? I know thread Trace with &p_trace=YES but this is not a solution because I want to start from sqlplus (PL/SQL code).

      Any help?

      Brg

      Damir

        • 1. Re: Trace Apex session from sqlplus
          Damir Vadas2

          Hi,

           

          Is this really so simple that it is not worth of replying or is it somewhere explained or what-do not understand.

          However, I'm pretty stuck on this so any hint/help will be a big relief.

          Brg

          Damir

          • 2. Re: Trace Apex session from sqlplus
            user503699

            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" ?

             


            • 3. Re: Trace Apex session from sqlplus
              Damir Vadas2

              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).

               

              Another why:

              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)

              Brg

              Damir Vadas

              • 4. Re: Trace Apex session from sqlplus
                user503699

                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

                • 5. Re: Trace Apex session from sqlplus
                  Damir Vadas2

                  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.

                  Brg

                  Damir

                  • 6. Re: Trace Apex session from sqlplus
                    user503699

                    Damir,

                     

                    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.

                    • 7. Re: Trace Apex session from sqlplus
                      Damir Vadas2

                      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....

                      :-)

                      Brg

                      Damir

                      • 10. Re: Trace Apex session from sqlplus
                        Tim St. H.

                        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.

                        ORACLE-BASE - SQL trace, 10046, trcsess and tkprof in Oracle

                         

                        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.

                         

                        Regards,

                        Tim St.

                        • 11. Re: Trace Apex session from sqlplus
                          Damir Vadas2

                          Hi,

                           

                          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.

                           

                          Brg,

                          Damir Vadas

                          • 12. Re: Trace Apex session from sqlplus
                            Tim St. H.

                            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.