SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqlthistpurge.log;
REM
REM $Header: 215187.1 sqlthistpurge.sql 11.2.9.6 2009/07/23 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqlthistpurge.sql
REM
REM DESCRIPTION
REM   This script purges a list of statements from the repository
REM
REM PRE-REQUISITES
REM   1. The user that executes this script can be SQLTXPLAIN or the
REM      application user
REM
REM PARAMETERS
REM   1. First Statement in range to be purged (required)
REM      A list of statement ids is presented to the user executing
REM      this script.
REM   2. Last Statement in range to be purged (required)
REM      A list of statement ids is presented to the user executing
REM      this 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 sqlthistpurge.sql passing range of statement
REM      ids to be purged (parameters can be passed inline or until
REM      requested)
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus sqltxplain/[pwd]
REM   SQL> start sqlthistpurge.sql [statement id 1] [statement id 2]
REM   SQL> start sqlthistpurge.sql s2263_olc510_apperf02 s2267_olc510_apperf02
REM   SQL> start sqlthistpurge.sql s2263 s2267
REM   SQL> start sqlthistpurge.sql
REM
REM NOTES
REM   1. For possible errors see sqlthistpurge.log
REM
SET ECHO OFF FEED OFF HEA ON LIN 200 PAGES 100 TRIMS ON TIM OFF;
PRO
COL sql_text FOR A50;
COL statement_id FOR A30;
SELECT statid statement_id,
       TO_CHAR(sqlt_date, 'DD-MON-YY HH24:MI:SS') sqlt_date,
       SUBSTR(REPLACE(TRIM(sql_text), CHR(10), ' '), 1, 50) sql_text
  FROM sqltxplain.sqlt$_statement
 ORDER BY 1, 2;
PRO
PRO Parameters:
PRO ~~~~~~~~~~
PRO Parameter 1: First Statement in range to be purged (required)
PRO Parameter 2: Last Statement in range to be purged (required)
PRO
PRO Enter range of ids to be purged (case sensitive)
PRO
DEF statement1 = '&1';
DEF statement2 = '&2';
PRO
PRO Value passed to sqlthistpurge.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 ... purging repository for statements between &&statement1 and &&statement2
PRO
BEGIN
  sqltxplain.sqlt$r.purge_repository (
    p_statement_id1   => '&&statement1',
    p_statement_id2   => '&&statement2' );
END;
/
SPO OFF;
CL COL;
UNDEFINE 1 2 statement1 statement2;
PRO SQLTHISTPURGE completed.
