Oracle 11g, show running queries [duplicate]

Question

I've a web application which submits some queries to Oracle 11g. I want to know what are these queries, exactly.

I there a way I do this in Oracle 11g? Couldn't do this in the web application itself.

Answer 1

I've a web application which submits some queries to Oracle 11g ... Couldn't do this in the web application itself.

Did you write the application?

• If so, you should have written code in there to give yourself the opportunity to see/record the SQL before it goes to the database.
• If not, then ask the supplier of the web application how to obtain this.

I want to know what are these queries

Why?
Do you think one of these queries is causing problems?

Answer 2

I copied this from https://community.oracle.com/thread/2354739 If you know how the application connects (what username), you can limit the query to that user.

select sesion.sid,
  sql_text
  from v$sqltext sqltext, v$session sesion
  where sesion.sql_hash_value = sqltext.hash_value
    and sesion.sql_address = sqltext.address
    and sesion.username is not null
  order by sqltext.piece;

Answer 3

You can enable tracing with DBMS_MONITOR in existing sessions or new sessions based on filtering the service name, module, action, client identifier.

If you do not know those details or want something simple, and you know the database user the application uses, you can enable tracing for sessions with a logon trigger, example: http://www.oracle-wiki.net/startscriptlogontrgtrace

--
-- Trigger Name: USER_TRACE_TRG
--
-- Description: Used to SQL trace a user session.
--
-- Add/Remove the traces you require
 
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = '&USER_ID'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
--   execute immediate 'alter session set events ''10046 level 1''; -- 11g onwatds simplier syntax is available. 
--   execute immediate 'alter session set events ''8103 trace name errorstack level 3''';
--   execute immediate 'alter session set events ''10236 trace name context forever, level 1''';
--   execute immediate 'alter session set max_dump_file_size=''UNLIMITED''';
--   execute immediate 'alter session set db_file_multiblock_read_count=1';
--   execute immediate 'alter session set tracefile_identifier=''ORA8103''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

The trace files will be created in the diag trace location of the database (select value from v$diag_info where name = 'Diag Trace';) and they will contain all statements executed in the sessions.

Tracing is more complex topic, this I posted just to keep it simple and give an idea.