SPO sqltrcasplit_error.log;
@@sqltcommon1.sql
REM $Header: 224270.1 sqltrcasplit.sql 11.4.5.0 2012/11/21 carlos.sierra $
REM
REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM   mauro.pagano@oracle.com
REM
REM SCRIPT
REM   sqltrcasplit.sql
REM
REM DESCRIPTION
REM   This script inputs a sql trace file that includes trace lines
REM   generated by EVENT 10046 and some other(s) like 10053, and it
REM   outputs two files. One with the trace lines matching the
REM   grammar of EVENT 10046 and the other with everything else. If
REM   input trace was generated by combined EVENTs 10046 and 10053
REM   then the output files correspond to each EVENT separtely.
REM
REM PARAMETERS
REM   1. Trace file name on UDUMP.
REM
REM EXECUTION
REM   1. Start SQL*Plus connecting as application user that generated
REM      trace to be analyzed
REM   2. Execute script sqltrcasplit.sql passing name of trace file
REM      was generated by EVENT 10046 and some other (i.e. 10053).
REM
REM EXAMPLES
REM   # cd sqlt/run
REM   # sqlplus [apps user]
REM   SQL> start sqltrcasplit.sql [name of your trace file]
REM   SQL> start sqltrcasplit.sql largesql.trc  <== your trace file
REM   Notice that your trace file must end with ".trc".
REM
REM NOTES
REM   1. If you need the input directory be something else than
REM      SQLT$STAGE use then sqlt/utl/sqltcdirs.sql connected as SYS.
REM   2. For possible errors see sqltrcasplit_error.log generated under
REM      the SQL*Plus default directory.
REM
@@sqltcommon2.sql
PRO Parameter 1:
PRO Trace Filename (required)
PRO
DEF input_filename = ^1;
PRO
PRO Value passed to sqltrcasplit.sql:
PRO TRACE_FILENAME: ^^input_filename.
PRO
EXEC DBMS_APPLICATION_INFO.SET_MODULE('sqltrcasplit', 'script');
PRO
PRO Splitting ^^input_filename.
PRO
SET TERM OFF HEA ON LIN 2000 PAGES 1000 TRIMS ON TI OFF TIMI OFF;
@@sqltcommon4.sql
EXEC ^^tool_administer_schema..trca$p.set_nls;
EXEC ^^tool_administer_schema..trca$g.general_initialization;
VAR v_tool_execution_id NUMBER;
EXEC :v_tool_execution_id := ^^tool_administer_schema..trca$p.get_tool_execution_id;
COL tool_execution_id NEW_V tool_execution_id FOR A17;
SELECT TO_CHAR(:v_tool_execution_id) tool_execution_id FROM DUAL;
PRO In case of unexpected termination, review file
PRO trca_e^^tool_execution_id..log from output directory
SET TERM ON;
PRO To monitor progress, login into another session and execute:
PRO SQL> SELECT * FROM ^^tool_administer_schema..trca$_log_v;;
PRO
PRO ... splitting trace(s) ...
PRO
SET TERM ON ECHO OFF VER OFF;
EXEC ^^tool_administer_schema..trca$i.trcanlzr(p_file_name => '^^input_filename.', p_analyze => 'NO', p_split => 'YES', x_tool_execution_id => :v_tool_execution_id, p_directory_alias_in => 'SQLT$STAGE');
SET TERM OFF;
COL trace_file_name NEW_V trace_file_name FOR A640;
SELECT ^^tool_administer_schema..trca$g.get_1st_trace_path_n_name trace_file_name FROM DUAL;
SET TERM ON;
PRO
PRO
PRO Trace Split completed.
PRO Review first sqltrcasplit_error.log file for possible fatal errors.
PRO Review next trca_e^^tool_execution_id..log for parsing messages and totals.
PRO
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 TI OFF TIMI OFF ARRAY 100 SQLP SQL> BLO . RECSEP OFF APPI OFF SERVEROUT ON SIZE 1000000 FOR TRU;
SET SERVEROUT ON SIZE UNL;
COL column_value FOR A2000;
WHENEVER OSERROR CONTINUE;
WHENEVER SQLERROR CONTINUE;
PRO No fatal errors!
SPO trca_e^^tool_execution_id._not_10046.trc;
SELECT column_value FROM TABLE(^^tool_administer_schema..trca$g.display_file(:v_tool_execution_id, '10053'));
SPO trca_e^^tool_execution_id._10046.trc;
SELECT column_value FROM TABLE(^^tool_administer_schema..trca$g.display_file(:v_tool_execution_id, '10046'));
SPO trca_e^^tool_execution_id..log;
SELECT column_value FROM TABLE(^^tool_administer_schema..trca$g.display_file(:v_tool_execution_id, 'LOG'));
SPO OFF;
HOS zip -m trca_e^^tool_execution_id. trca_e^^tool_execution_id.* sqltrcasplit_error.log
HOS zip -d trca_e^^tool_execution_id. sqltrcasplit_error.log
--HOS unzip -l trca_e^^tool_execution_id.
SET TERM ON;
PRO
PRO File trca_e^^tool_execution_id..zip has been created.
@@sqltcommon9.sql
PRO SQLTRCASPLIT completed.
