Oracle 9i provides a way of gathering previously executed SQL in a system when tracing was not enabled. This feature is possible through the system view V$SQL, while not as robust as the newer Oracle releases, it still provides the DBA with the ability to view the exact user activities which are occurring in the database. Viewing previously executed statements during and after a system slowdown is highly valuable in determining the cause of certain system problems. In this article we will review the system view V$SQL and the columns in it which would allow you to regenerate previously executed statements.
1. Logon to SQL*PLUS as sysdba.
mylinux$ sqlplus ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Tue Aug 16 10:53:45 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL>
2. Describe the system view V$SQL and you will find three columns of interest for extraction of SQL are: SQL_TEXT, PARSING_USER_ID, and LAST_LOAD_TIME. SQL_TEXT provides the first 1000 characters of the SQL statement executed. NOTE: this may not be the entire SQL text. PARSING_USER_ID provides the User ID which last successfully parsed the statement. NOTE: This may not be the last person to execute the statement. Finally, LAST_LOAD_TIME provides you the time of last execution.
SQL> desc v$sql
Name Null? Type
—————————————– ——– —————
SQL_TEXT VARCHAR2(1000)
PARSING_USER_ID NUMBER
LAST_LOAD_TIME VARCHAR2(19)
…
…
…
3. Due to the limitation of DBMS_OUTPUT package in 9i we will create a dummy table to temporarily hold our SQL extracts. Execute the collowing DDL to create the table SQL_LONG.
SQL> create table sql_long(sql_text long, exec_time varchar2(19));
Table created.
SQL>
4. To extract all SQL in your database since instance startup, execute the following PL/SQL Block to populate your dummy table SQL_LONG.
set serveroutput on size 1000000
declare
v_ct number;
begin
for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql order by last_load_time)
loop
insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);
end loop;
end;
/
5. Now the only thing left is to extract the SQL from your dummy table. NOTE: You do not have to order by time of execution, because the SQL was inserted in ordered fashion.
set long 10000
select * from sql_long;
6. To retrieve the currently executing SQL statements issue the following PL/SQL block. NOTE: truncate the table SQL_LONG before every execution to ensure that you only received the information from the previous block.
Truncate table sql_long;
set serveroutput on size 1000000
declare
v_ct number;
begin
for v_sql in(select SQL_TEXT, LAST_LOAD_TIME from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
last_load_time)
loop
insert into sql_long(sql_text, exec_time) values(v_sql.sql_text,
v_sql.last_load_time);
end loop;
end;
/
set long 10000
select * from sql_long;
Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com