Show all queries coming to an Oracle database

Question

I need to see all queries coming to database. How to do that? I could not get proper results from a Google search.

Answer 1

Enable SQL Trace & all the queries coming to the database will be logged.

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

The trace file will be present in the udump directory.

If you want to audit the database, look at my previous answer.

Answer 2

If you need to see all queries from all sessions for a SHORT window of time and you need a really simple solution, this is what I do. (The above answers will only show you SQL being run in one session, this gives all SQL across all sessions, easily.)

1). Create a temp table to store all the retrieved SQL:

-- Fabien pointed out out that 'port may be inaccessible on 10.2 
       CREATE TABLE "MIKE"."TMP" 
       (    "LOOP_NO" NUMBER(10,0), 
        "SID" NUMBER, 
        "SERIAL#" NUMBER, 
        "PROCESS" VARCHAR2(24 BYTE), 
        "PROGRAM" VARCHAR2(48 BYTE), 
        "MODULE" VARCHAR2(64 BYTE), 
        "OSUSER" VARCHAR2(30 BYTE), 
        "SCHEMANAME" VARCHAR2(30 BYTE), 
        "ACTION" VARCHAR2(64 BYTE), 
        "MACHINE" VARCHAR2(64 BYTE), 
        "TERMINAL" VARCHAR2(30 BYTE), 
        "ADDRESS" RAW(8), 
        "PIECE" NUMBER, 
        "SQL_TEXT" VARCHAR2(4000)
       )

2). Run a nasty polling loop in an anonymous block to gather all SQL run on the system, as long as the block is running:

declare
begin 
  for j in 1.. 1000 loop 
  
     insert into  mike.tmp
     SELECT j, b.sid, b.serial#, b.process, b.program, b.module, b.osuser, b.schemaname, b.action, b.machine, b.terminal,a.address,  a.piece, a.sql_text
            FROM V$sqltext_With_Newlines a
            join V$Session b  on a.address = b.sql_address
           WHERE A.ADDRESS NOT IN (select address FROM mike.tmp)
        ORDER BY b.sid, a.piece;
  
    commit;
  
  end loop;
end;

3). Query to retrieve SQL:

select distinct osuser, a.address, a.sid, a.piece, a.sql_text 
          from mike.tmp a
          join (select loop_no, sid from mike.tmp where sql_text like '%anytexthere%') b 
            on a.loop_no = b.loop_no
           and a.sid = b.sid 
           order by a.sid, a.address, a.piece

... please mind that this is just a quick way to trap SQL when you are in a "what the heck is going on here?" situation, and you DO NOT have GUI Tools, and you DO NOT have file access to USER_DUMP_DEST.

Answer 3

Running XE in Windows here is how I do it to find what user is doing. Start up SQLPlus and run:

> SELECT USERNAME, SID, SERIAL# FROM v$session WHERE userName = '<your user>'

This gets you two integer values, SID and SERIAL# for your user. Your user may have more than one session open. Run this to turn on logging:

> execute dbms_system.set_sql_trace_in_session(<SID>, <SERIAL#>, true)

Next have your app do some work... Find out where the data went using:

> SHOW PARAMETERS user_dump_dest

And you'll get something like: C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace where you will find a number of trace logs. The .trc files are simply text

When done, turn off the logging so you don't fill up files or slow your db down.

> execute dbms_system.set_sql_trace_in_session(<SID>, <SERIAL#>, false)

There you go - happy diagnosing and reverse engineering!