SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqlthistfile.log;
REM
REM $Header: 215187.1 sqlthistfile.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   sqlthistfile.sql
REM
REM DESCRIPTION
REM   This script extracts a historic report out of the repository
REM
REM PRE-REQUISITES
REM   1. Execute SQLT (any method) or use imp to populate table
REM      sqlt$_hist_files
REM   2. The user that executes this script can be SQLTXPLAIN or the
REM      application user
REM
REM PARAMETERS
REM   1. File Type (optional)
REM      A list of file types is presented to the user executing this
REM      script.
REM   2. File Name (required)
REM      A list of file names is presented to the user executing this
REM      script.
REM
REM EXECUTION
REM   1. Navigate to sqlt/run directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM   3. Execute script sqlthistfile.sql passing file type and name
REM      (parameters can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus sqltxplain/[pwd]
REM   SQL> start sqlthistfile.sql [file type] [file name];
REM   SQL> start sqlthistfile.sql LITE sqlt_s6927_sqltxplite.txt;
REM   SQL> start sqlthistfile.sql lite s2597;
REM   SQL> start sqlthistfile.sql;
REM
REM NOTES
REM   1. For possible errors see sqlthistfile.log
REM   2. You can import the reports from another system
REM      # imp sqltxplain tables='sqlt$_hist_files' file=sqlt_s9999.dmp ignore=y
REM
SET ECHO OFF FEED OFF HEA ON LIN 200 PAGES 100 TRIMS ON TIM OFF;
PRO
SELECT DISTINCT file_type
  FROM sqltxplain.sqlt$_hist_files
 ORDER BY 1;
PRO
PRO Enter file type (optional)
PRO
DEF file_type = '&1';
PRO
SELECT filename file_name, file_size, TO_CHAR(file_date, 'DD-MON-YY HH24:MI:SS') file_date
  FROM sqltxplain.sqlt$_hist_files
 WHERE UPPER(file_type) LIKE UPPER('%'||TRIM('&&file_type.')||'%')
 ORDER BY filename;
PRO
PRO Enter partial or complete file name (required)
PRO
DEF file_name = '&2';
PRO
PRO Value passed to sqlthistfile.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO FILE TYPE: &&file_type
PRO FILE NAME: &&file_name
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');
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
SELECT REPLACE(filename copy_file_name, '.', '_h.')
  FROM sqltxplain.sqlt$_hist_files
 WHERE UPPER(file_type) LIKE UPPER('%'||TRIM('&&file_type.')||'%')
   AND UPPER(filename) LIKE UPPER('%'||TRIM('&&file_name')||'%')
   AND ROWNUM = 1;
SELECT NVL('&&copy_file_name', 'sqlt_hist_file.log') copy_file_name FROM DUAL;
SET TERM ON;
PRO
PRO ... extracting file &&copy_file_name.
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_hist_file('&&file_type.', '&&file_name'));
SPO OFF;
SET TERM ON;
CL COL;
UNDEFINE 1 2 file_type file_name copy_file_name;
PRO SQLTHISTFILE completed.
