control tracefiles for other sessions [message #64771] |
Mon, 26 January 2004 09:28 |
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 |
Frank Naude
Messages: 4587 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 |
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
|
|
|