Oracle 10g allows for increase amount of information to be extracted about SQL statements executed against the database since last instance startup. In Oracle 10g we have the new V$BIND_VALUE_CAPTURE, which allows you see the bind variables attached to most SQL statements executed in the database. Additionally, the view V$SQL has several improvements over it’s 9i counterpart to include: Full contents of the SQL statement; last execution time in date format; and actually username of parsing user. This article will cover how to extract previously and currently executed SQL statements with a few different definable variables from you Oracle 10g database.
1. Logon to your Oracle 10g database as sysdba.
MyLinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 16 13:30:13 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. To extract all SQL executed since the last system startup, execute the following PL/SQL block. NOTE: This may contain a lot of information, so you should spool the contents of this execution to an OS layer file with the command: spool ./
/********************
// Larry Catt
// 15 AUG 2009
// All SQL since instance startup.
//
// 10g version
/***********************/
set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
begin
for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql order by LAST_ACTIVE_TIME)
loop
dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);
for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop
dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/
3. The following PL/SQL block will give you all the SQL for a particular user defined by the variable V_USER.
/********************
// Larry Catt
// 15 AUG 2009
// ALL SQL from a particular user defined as the variable V_USER.
//
// 10g version
/***********************/
set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
v_user varchar2(30):=’SYS’;
begin
for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where parsing_schema_name= v_user order by LAST_ACTIVE_TIME)
loop
dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);
for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop
dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/
4. The following PL/SQL block will give you all the SQL statements executed during the time range of variables V_START_TIME and V_END_TIME.
/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements executed between the times V_START_TIME and V_END_TIME.
//
// 10g version
/***********************/
set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
v_start_time date := to_date(’15-AUG-2009 13:15:00′,’DD-MON-YYYY HH24:MI:SS’);
v_end_time date:=to_date(’15-AUG-2009 15:15:00′,’DD-MON-YYYY HH24:MI:SS’);
begin
for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where LAST_ACTIVE_TIME between v_start_time and
v_end_time order by LAST_ACTIVE_TIME)
loop
dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);
for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop
dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/
5. The following PL/SQL block will give you all the SQL statements currently being executed in the system.
/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements currently executing.
//
// 10g version
/***********************/
set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
begin
for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
LAST_ACTIVE_TIME)
loop
dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);
for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop
dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/
6. This completes extraction of Oracle SQL statements in Oracle 10g.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com