V$SQLTEXT_WITH_NEWLINES

SQL statements including newline characters.

Columns
   ___________________________
 
   ADDRESS
   HASH_VALUE
   COMMAND_TYPE
   PIECE
   SQL_TEXT

This view can be used to construct the entire text for each session's actual SQL statement, via adp-gmbh.ch

set serveroutput on size 1000000

declare
  v_stmt     varchar2(16000);
  v_sql_text v$sqltext_with_newlines.sql_text%type;
  v_sid      v$session.sid%type;
begin
  for r in (
    select 
        sql_text,s.sid
    from 
      v$sqltext_with_newlines t, 
      v$session s 
    where 
      s.sql_address=t.address
    order by s.sid, piece) loop

    v_sid := nvl(v_sid,r.sid);

    if v_sid <> r.sid then
      dbms_output.put_line(v_sid);
      put_line(v_stmt,100);
      v_sid  := r.sid; 
      v_stmt := r.sql_text;
    else
      v_stmt := v_stmt || r.sql_text;
    end if;
    
  end loop;
  dbms_output.put_line(v_sid);
  dbms_output.put_line(v_stmt,100);

end;
/

Related:

V$OPEN_CURSOR 
V$SQL 
V$SQLAREA 
V$SQLTEXT 
V$SQL_BIND_DATA 
V$SQL_BIND_METADATA 
V$SQL_CURSOR 
V$SQL_SHARED_MEMORY


© Copyright SS64.com 1999-2015
Some rights reserved