Home » RDBMS Server » Performance Tuning » control tracefiles for other sessions
control tracefiles for other sessions [message #64771] Mon, 26 January 2004 09:28 Go to next message
ilver
Messages: 50
Registered: January 2004
Member
Like setting the tracefile_identifier for "my current session" using alter session set tracefile_parameter 'my_stmt'. I need to set the tracefile_paramenter for another session.

Using dbms_system I can alter any boolean or numeric parameter in other sessions, but how can I manipulate the tracefile_patrameter in other sessions ?

 

 

 
Re: control tracefiles for other sessions [message #64773 is a reply to message #64771] Tue, 27 January 2004 03:37 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

You are right, one can only change integer and boolean parameters for "other sessions". This is because the DBMS_SYSTEM package (in 9.2.0 and 10g) contains procedures SET_INT_PARAM_IN_SESSION and SET_BOOL_PARAM_IN_SESSION, but no procedure for SET_CHAR_PARAM_IN_SESSION.

SQL> exec DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION( -
>    23, 2, 'sort_area_size', 64*1024*1024);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION( -
> 23, 2, 'sql_trace', FALSE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SYSTEM.SET_CHAR_PARAM_IN_SESSION( -
>    23, 2, 'tracefile_identifier', '123');
                  *
ERROR at line 1:
ORA-06550: line 1, column 19:
PLS-00302: component 'SET_CHAR_PARAM_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Best regards.

Frank
Re: control tracefiles for other sessions [message #64774 is a reply to message #64771] Tue, 27 January 2004 07:02 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
If you are Ok with enabling tracing with triggers automatically, then you can do something like this..

SQL> create or replace trigger trace_trigger_on 
   AFTER LOGON ON DATABASE
   declare
   stmt varchar2(100);
   hname varchar2(20);
   uname varchar2(20);
   begin
   select sys_context('USERENV','HOST'),sys_context('USERENV','SESSION_USER') into hname,uname from dual;
  2     stmt := 'alter session set tracefile_identifier='||hname||'_'||uname; 
  3     EXECUTE IMMEDIATE stmt;  
  4     EXECUTE IMMEDIATE 'alter session set sql_trace=true';
  end;  5    6    7    8    9   10   11   12  
 13  /

Trigger created.

SQL> connect scott/tiger
Connected.
SQL> select * from dual;

D
-
X

SQL> disconnect

the resulting trace file looks like 
-rw-r-----   1 oracle   dba         7919 Jan 27 11:57 dbatest_ora_6948_SAMGDEAB06_SCOTT.trc



ofcourse, you will need to disable the trigger when not needed. Maybe there are other ways,but I cant think of anything else for now..

-Thiru
Previous Topic: splitting datafiles
Next Topic: How long each commit took?
Goto Forum:
  


Current Time: Thu Oct 01 02:42:04 CDT 2020