SET DEF ^ ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqltxtract.log;
REM
REM $Header: 215187.1 sqltxtract.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqltxtract.sql
REM
REM DESCRIPTION
REM   This script generates a set of comprehensive reports with query
REM   tuning diagnostic details. It requires as input the hash_value
REM   or the sql_id of one memory-resident (gv$sql) SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Determine the hash_value or sql_id of one SQL that still
REM      resides in memory (v$sql or gv$sql).
REM   3. To get plan statistics, STATISTICS_LEVEL must be set to ALL
REM      before the desired SQL is executed
REM   4. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM
REM PARAMETERS
REM   1. Unique id of the SQL to be extracted (required)
REM      (hash_value or sql_id would work)
REM
REM EXECUTION
REM   1. Navigate to sqlt/run server directory
REM   2. Start SQL*Plus in server connecting as application user
REM   3. Execute script sqltxtract.sql passing hash_value or sql_id
REM      of one memory-resident SQL
REM   4. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxtract.sql [hash_value or sql_id for SQL]
REM   SQL> start sqltxtract.sql 2524255098  <== SQL hash_value
REM   SQL> start sqltxtract.sql 0w6uydn50g8cx  <== SQL sql_id
REM
REM NOTES
REM   1. For a very lite version use sqltxtlite.sql instead (10g+)
REM   2. For alternative parameters use sqltxtract2.sql instead
REM   3. For possible errors see sqltxtract.log
REM
SET ECHO OFF;
PRO
PRO Parameter 1: SQL_ID or HASH_VALUE of the SQL to be extracted (required)
DEF unique_id = '^1';
PRO
PRO Value passed to sqltxtract.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO UNIQUE_ID: ^^unique_id
PRO
SET TERM OFF HEA ON LIN 2000 PAGES 1000 TRIMS ON TIM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PRO
PRO NOTE:
PRO If you get an ORA-06550 followed by PLS-00302: component 'INITIALIZATION' must be declared,
PRO then review *.lis files created during installation and fix errors reported.
PRO
EXEC sqltxplain.sqlt$r.initialization;
DEF _SQLPLUS_RELEASE
COL connected_user NEW_V connected_user FOR A30;
SELECT user connected_user FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') current_time FROM DUAL;
SELECT * FROM v$version;
COL library FOR A64 HEA 'Libraries';
SELECT object_type||' '||object_name||' ('||status||')' library
  FROM all_objects
 WHERE owner = 'SQLTXPLAIN'
   AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
 ORDER BY
       object_type, object_name;
COL tool_version FOR A16 HEA 'Tool Version';
SELECT sqltxplain.sqlt$d.get_param('tool_version', 'I') tool_version FROM DUAL;
COL install_date FOR A16 HEA 'Install Date';
SELECT sqltxplain.sqlt$d.get_param('install_date', 'I') install_date FROM DUAL;
COL host_name FOR A128 HEA 'Host Name';
SELECT sqltxplain.sqlt$d.get_param('host_name_short', 'I') host_name FROM DUAL;
COL column_value FOR A128 HEA 'Directories';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.directories);
COL column_value FOR A128 HEA 'Libraries';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.packages);
EXEC sqltxplain.sqlt$d.validate_tool_version('11.2.9.8');
SET TERM ON;
PRO
PRO
PRO NOTE:
PRO You connected as ^^connected_user
PRO If you get an error please verify the following:
PRO 1. That you connected as the application user that executed original SQL.
PRO 2. That your SQL does exists in V$SQL or AWR.
PRO
PRO ... please wait
PRO
SET TERM OFF;
VAR v_statement_id VARCHAR2(256);
VAR v_stat_id VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$i.sqltxtract (
    p_unique_id    => '^^unique_id',
    x_statement_id => :v_statement_id,
    x_stat_id      => :v_stat_id );
END;
/
COL statement_id2 NEW_V statement_id2 FOR A16;
COL server_directory NOPRI NEW_V server_directory FOR A512;
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
COL tc_script_file_name NOPRI NEW_V tc_script_file_name FOR A256;
COL tc_builder_script_file_name NOPRI NEW_V tc_builder_script_file_name FOR A256;
SELECT :v_statement_id statement_id2, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... sqlt_s^^statement_id2._* files have been created into server directory:
PRO ... ^^server_directory
PRO ... copying now generated files into local directory
PRO
SET TERM OFF ECHO OFF FEED OFF FLU OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 SHOW OFF SQLC MIX TAB OFF TRIMS ON VER OFF TIM OFF ARRAY 100 SQLP SQL> BLO . RECSEP OFF SERVEROUT ON SIZE 1000000 FOR TRU;
COL column_value FOR A2000;
WHENEVER OSERROR CONTINUE;
WHENEVER SQLERROR CONTINUE;
PRO No fatal errors!
SPO OFF;
SELECT NVL(REPLACE(file_sqlt_main, '.', '_c.'), 'sqlt_s^^statement_id2._main.html') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('MAIN', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_frames, '.', '_c.'), 'sqlt_s^^statement_id2._frames.html') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('FRAMES', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_metadata, '.', '_c.'), 'sqlt_s^^statement_id2._metadata.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('METADATA', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_setenv, '.', '_c.'), 'sqlt_s^^statement_id2._setenv.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('SETENV', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_readme, '.', '_c.'), 'sqlt_s^^statement_id2._readme.txt') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('README', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_trace, '.', '_c.'), 'sqlt_s^^statement_id2._trace.trc') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRACE', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_lite, '.', '_c.'), 'sqlt_s^^statement_id2._lite.txt') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('LITE', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_tcscript, '.', '_c.'), 'sqlt_s^^statement_id2._tc_script.sql') tc_script_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^tc_script_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TCSCRIPT', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_tcsql, '.', '_c.'), 'sqlt_s^^statement_id2._tc_sql.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TCSQL', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_tcbuilder, '.', '_c.'), 'sqlt_s^^statement_id2._tc_builder.sql') tc_builder_script_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^tc_builder_script_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TCBUILDER', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
@^^tc_builder_script_file_name.
SPO sqlt_s^^statement_id2._xtract.log;
GET sqltxtract.log
.
SET TERM ON
PRO
PRO ... exporting SQLT tables needed to create a test case
PRO
PRO ... if sqlt_s^^statement_id2..dmp is not created, please execute manually from server:
PRO ... # exp sqltxplain@host_string tables=sqlt% file=sqlt_s^^statement_id2..dmp statistics=none log=sqlt_s^^statement_id2._exp.log
PRO
ACC sqltxplain_password PROMPT '...please enter SQLTXPLAIN password: ' hide;
ACC host_string PROMPT'...please specify OPTIONAL Host String (i.e.: @PROD, or just hit enter): ';
HO exp sqltxplain/^^sqltxplain_password^^host_string tables=sqlt% file=sqlt_s^^statement_id2..dmp statistics=none log=sqlt_s^^statement_id2._exp.log
UNDEFINE SQLTXPLAIN_PASSWORD
PRO
PRO ... generating now a zip with all generated files
PRO
PRO ... if sqlt_s^^statement_id2..zip is not created, please execute manually:
PRO ... # zip -mT sqlt_s^^statement_id2. sqlt_s^^statement_id2.* sqltxtract.log
PRO ... # zip -d sqlt_s^^statement_id2. sqltxtract.log
PRO
SPO OFF;
HO zip -mT sqlt_s^^statement_id2. sqlt_s^^statement_id2.* sqltxtract.log
HO zip -d sqlt_s^^statement_id2. sqltxtract.log
CL COL;
UNDEFINE 1 unique_id server_directory copy_file_name host_string;
SET DEF ON
PRO SQLTXTRACT completed.
