제목: How to generate a Trace file with user defined string suffixed
The information in this document applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1
Information in this document applies to any platform.
Goal
-- You have many developers working on query tuning in client server mode.
-- They are enabling SQL_TRACE at session level to diagnose the query tuning issue.
-- As many of them are working in parallel, USER_DUMP_DEST is flooded with many more trace files and its difficult to trace the which belongs to whom.
-- You are looking for some way which can help manually naming the trace file or prefix/ suffix some identifier string automatically.
Fix
-- You can suffix the OSUSER or desired string to a trace file name using a parameter called TRACEFILE_IDENTIFIER.
-- TRACEFILE_IDENTIFIER specifies a custom identifier that becomes part of the Oracle Trace file name. Such a custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.
-- Each time this parameter is dynamically modified, the next trace dump will be written to a trace file which has the new parameter value embedded in its name. Trace file continuity information is automatically added to both the old and new trace files to indicate that these trace files belong to the same process.
-- This parameter can only be used to change the name of the foreground process' trace file; the background processes continue to have their trace files named in the regular format. For foreground processes, the TRACEID column of the V$PROCESS view contains the current value of the TRACEFILE_IDENTIFIER parameter. When this parameter value is set, the trace file name has the following
format: sid_ora_pid_traceid.trc
Example 1
=========
-- Suffixing a user defined identifier string manually.
-- In this example, sid is the oracle instance ID, pid is the process ID, and trace id is the value of the
TRACEFILE_IDENTIFIER parameter.
SQL> alter session set tracefile_identifier='ARUL';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from dual;
D
-
X
SQL> alter session set sql_trace =false;
Session altered.
-- The trace generated in UDUMP will be named as "ora925_ora_2740_arul.trc"
Example 2
=========
-- Suffixing OS_USERNAME as an identifier automatically via logon trigger.
-- Create a logon trigger under SYS schema as follows
CREATE OR REPLACE TRIGGER TRACE_IDENTIFIER_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_trace_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO v_trace_identifier FROM dual;
execute immediate 'alter session set tracefile_identifier = '||v_trace_identifier;
END;
/