SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqltcompare.log;
REM
REM $Header: 215187.1 sqltcompare.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   sqltcompare.sql
REM
REM DESCRIPTION
REM   This script compares two prior executions of SQLT:
REM     Environment and SQL identification
REM     SQL Statement text
REM     Explain Plan
REM     Tables, Indexes and Partitions
REM     CBO Statistics
REM     Optimizer Environment
REM     Initialization Parameters
REM     BUG Fix Control Environment
REM
REM PRE-REQUISITES
REM   Steps 1 to 3 are necessary if SQLs to compare are from
REM   different systems.
REM   1. Execute SQLT (any method) in source systems for the one SQL
REM      statement that will be compared
REM   2. Transfer BINARY files sqlt.dmp from sources to destination
REM      system
REM   3. Import these tables into target system using commands:
REM      # imp sqltxplain/[pwd] tables='sqlt$_statement' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_plan_table' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_stattab' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_parameter2' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_indexes' file=sqlt.dmp ignore=y
REM   4. The user that executes this script can be SQLTXPLAIN or the
REM      application user
REM
REM PARAMETERS
REM   1. Statement ID 1 as per SQLT for SQL in source system(required)
REM      A list of statement ids is presented to the user executing
REM      this script.
REM   2. Statement ID 2 as per SQLT for SQL in source system (required)
REM      A list of statement ids is presented to the user executing
REM      this script.
REM
REM EXECUTION
REM   1. Navigate to sqlt/utl directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM   3. Execute script sqltcompare.sql passing statement id and
REM      schema (parameters can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus sqltxplain/[pwd]
REM   SQL> start sqltcompare.sql [statement id 1] [statement id 2];
REM   SQL> start sqltcompare.sql s2263_olc510_apperf02 s2597_scmx7st;
REM   SQL> start sqltcompare.sql s2263 s2597;
REM   SQL> start sqltcompare.sql;
REM
REM NOTES
REM   1. For possible errors see sqltcompare.log
REM
SET ECHO OFF FEED OFF HEA ON LIN 200 PAGES 100 TRIMS ON TIM OFF;
PRO
COL sql_text FOR A50;
SELECT statid statement_id,
       TO_CHAR(sqlt_date, 'DD-MON-YY HH24:MI:SS') sqlt_date,
       SUBSTR(REPLACE(TRIM(NVL(sql_text_clob_stripped, sql_text)), CHR(10), ' '), 1, 50) sql_text
  FROM sqltxplain.sqlt$_statement
 ORDER BY 1, 2;
PRO
PRO Parameters:
PRO ~~~~~~~~~~
PRO Parameter 1: Statement id 1 to be compared (required)
PRO Parameter 2: Statement id 2 to be compared (required)
PRO
PRO Enter partial or complete ids to be compared
PRO
DEF statement1 = '&1';
DEF statement2 = '&2';
PRO
PRO Values passed to sqltcompare.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO STATEMENT 1: &&statement1
PRO STATEMENT 2: &&statement2
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;
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.2');
SET TERM ON;
PRO
PRO ... generating sqlt compare report for &&statement1 and &&statement2
PRO
SET TERM OFF;
VAR v_output_filename VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$r.compare_report (
    p_statement_id1   => '&&statement1',
    p_statement_id2   => '&&statement2',
    x_output_filename => :v_output_filename );
END;
/
COL server_directory NOPRI NEW_V server_directory FOR A512;
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
SELECT :v_output_filename copy_file_name, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... &&copy_file_name. file has been created into server directory:
PRO ... &&server_directory
PRO ... copying now generated file into local directory
PRO
SET TERM OFF ECHO OFF DEF ON 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;
SPO &&copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('COMPARE', NULL, :v_output_filename));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SET TERM ON;
CL COL;
UNDEFINE 1 2 statement1 statement2 server_directory copy_file_name;
PRO SQLTCOMPARE completed.
