Oracle SQL Query Logging

Oracle SQL Query logging

If you're using a modern version of the database (9i or later) and you have an Enterprise Edition license you can use Fine-Graining Auditing. It allows us to audit user queries at a very low level of granularity, through defined policies.

To capture SQL text and bind variables you will need to set the AUDIT_TRAIL parameter appropriately when adding an FGA Policy. Find out more.

"i'm using an 11g standard, so auditing functions are not supported."

Not exactly. The AUDIT command is part of the standard Oracle build, but it only allows us to capture when a given user issues a SELECT against a given table. But, yes, to find out exactly what they are selecting requires Enterprise Edition license.

Also there is no ON SELECT trigger, so we cannot roll our own.

"So can i use AUDIT command in the standard edition? ... But then a
consultant told me, that i cannot use it without paying enterprise
license? "

Speaking as a consultant myself, I do have to say those guys don't always know what they are talking about.

So let's be clear:

the AUDIT command is part of Oracle SQL. It is usable with the Standard Edition. In fact since 11g it is enabled by default. It audits general activity. Find out more.
Fine Grained Auditing is a PL/SQL package with is only usable if you have the Enterprise Edition. It allows us to audit user activity at a very low level. Find out more.

Oracle - Log the executed query

Unfortunately, this field restricted by 1Kb

If you need the full SQL, then use the SQL_FULLTEXT which is a CLOB datatype instead of SQL_TEXT whcih is limited to first 1000 characters.

From documentation,

Column          Datatype        Description
------          --------------  ---------------------------------------

SQL_TEXT        VARCHAR2(1000)  First thousand characters of the SQL 
                                text for the current cursor

SQL_FULLTEXT    CLOB            Full text for the SQL statement exposed 
                                as a CLOB column. The full text of a SQL
                                statement can be retrieved using this 
                                column instead of joining with the 
                                V$SQL_TEXT dynamic performance view.

So, use:

SELECT SQL_FULLTEXT FROM v$sql;

By the way, seems like you are actually looking for tracing your session to get the complete details of the procedure and the SQL statements involved. I would suggest to trace the session with level 4 i.e. with the addition of bind variable values.

See How to generate trace file – SQL Trace and TKPROF in Oracle

Inserting log statement inside SQL queries

I think it is not possible from SQL without looking on trace files. It is possible if you're going to insert row by row from PL/SQL:

declare
  cursor c is   select 999 a, 'Administration' b, 200 c, 1700 d from dual union all
                select 666 a, 'Marketing' b     , 201 c, 1800 d from dual;
begin
for x in c loop
 insert into departments(department_id, department_name, manager_id,location_id) values (x.a, x.b, x.c, x.d);
 dbms_output.put_line(x.a || ' '); --or insert to log table here
end loop;
  dbms_output.put_line('has been inserted');
end;

Oracle SQL query to fetch data from log table

This query, using function lead() displays all rows where stat_cd is Definite and prior row in order of tran_id:

select row_id, tran_id, tran_date, stat_cd 
  from (
    select data.*, 
        case when stat_cd='Definite' 
               or (lead(stat_cd) over (order by tran_id)) = 'Definite' then 1 
        end change
      from data )
  where change = 1 order by row_id, tran_id

^{SQLFiddle demo}

You may need to change over (order by tran_id) to over (partition by row_id order by tran_id) if your data is organized this way.

Edit: Modified query after additional informations were provided:

select row_id, tran_id, tran_date, stat_cd 
  from (
    select xyz.*,
        case 
          when stat_cd='Actual'   
               and (lead(stat_cd) over (order by tran_id)) = 'Definite' then 1 
          when stat_cd='Definite' 
               and (lag(stat_cd)  over (order by tran_id)) = 'Actual'   then 2 
        end change
      from xyz)
  where change is not null

^{SQLFiddle demo}

GeoServer: How to log SQL query?

As I mentioned in my comment to the OP, the VERBOSE_LOGGING profile provides detail that includes the SQL queries that GeoServer is running. The following links provide some added detail about logging in GeoServer.

Global Settings

Advanced Logging Config

Oracle XE query log

You would:

alter session set sql_trace=true;

The trace file will be in the udump subdirectory under the installation directory.

Edit: Actually the docs say that sql_trace is deprecated in 10g: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams205.htm#REFRN10208

It looks like DBMS_SESSION is the way to go now:

eg.

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#CHDDGCCB

How to view transaction logs in oracle of recent sql commands executed by a hibernate enabled java program (with time and performance info)

You can use v$sql, gv$sql, dba_hist_sqltext views. Just select from this view and you will find interesting data. Filter and Sort them according to your need.

select * from v$sql;

You can join them with v$session or gv$session to fetch the session details who executed which sql.

Cheers!!

Oracle SQL Query Logging