Question:
I'm looking for a way to extract the full text of a
SQL statement from a table into a flat file so that I can
tune the SQL. How do I extract a SQL statement?
Answer: In AWR the full SQL text reside
in the text column of the dba_hist_sqlstat table:
Without AWR, we seek those tables
that contain the sql_fulltext column. These
will be the full text of a SQL statement from the v$sql
view::
set long
30000000
spool
display_sql.lst
select
sql_fulltext
from
v$sql
where
lower(sql_fulltext) like
lower('%AUTHORS%');
This script will extract the full text of SQL statements
from AWR.
In the code below, we generate the SQL section for an AWR
report between snapshot 1230 and 1231:
select
*
from
table (dbms_workload_repository.awr_sql_report_text
(&v_db_id, &v_inst_num, :b_snap_id, :e_snap_id,
'&v_sql_id'));
select
*
from
table
(dbms_workload_repository.awr_sql_report_text
(1692970157, 1, 1230, 1231));
However, Oracle recommends using awrsqrpt.sql
instead of
awr_sql_report_text.
There is a script to extract SQL and execution plans from
AWR in $ORACLE_HOME/rdbms/admin/awrsqrpt.sql