SET DEF ^ ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqltxtlite.log;
REM
REM $Header: 215187.1 sqltxtlite.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   sqltxtlite.sql
REM
REM DESCRIPTION
REM   This script generates a lite report with query tuning
REM   diagnostic details. It requires as input the hash_value or the
REM   sql_id of one memory-resident (gv$sql) SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install SQLTXPLAIN tool as per instructions.txt provided
REM   2. Determine the hash_value or sql_id of one SQL that still
REM      resides in memory (gv$sql).
REM   3. 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 sqltxtlite.sql passing hash_value or sql_id
REM      of one memory-resident SQL
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxtlite.sql [hash_value or sql_id for SQL]
REM   SQL> start sqltxtlite.sql 2524255098  <==  SQL hash_value
REM   SQL> start sqltxtlite.sql 0w6uydn50g8cx  <==  SQL sql_id
REM
REM NOTES
REM   1. This script can only be used on 10g and higher
REM   2. For possible errors see sqltxtlite.log
REM
SET ECHO OFF;
PRO
PRO Parameter 1: SQL_ID or HASH_VALUE of the SQL to be extracted (required)
DEF unique_id2 = '^1';
PRO
PRO Value passed to sqltxtlite.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO UNIQUE_ID: ^^unique_id2
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.
PRO
PRO ... please wait
PRO
SET TERM OFF;
VAR v_statement_id VARCHAR2(256);
VAR v_stat_id VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$i.sqltxtlite (
    p_unique_id    => '^^unique_id2',
    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;
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._sqltxplite.txt file has been created into server directory:
PRO ... ^^server_directory
PRO ... copying now generated file 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 SQLERROR CONTINUE;
SPO OFF;
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;
SET TERM ON;
CL COL;
UNDEFINE 1 unique_id server_directory copy_file_name;
SET DEF ON
PRO SQLTXTLITE completed.
