How to Execute Alter Session Statements for Remote Sessions [ID 369154.1] Modified 22-JUN-2011     Type HOWTO     Status PUBLISHED

In this Document
  
  Solution

---

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 11.1.0.8 - Release: 8.1.5 to 11.1
Information in this document applies to any platform.

Goal

How can 'alter session' statements be issued so that a remote session started by a database link is the session that is affected?

Solution

There are two ways this can be done. 

1)  The first method involves creating a procedure on the remote database that issues the alter session statements, and then calling this procedure via a database link. For example, if you wanted to set the 10046 event for a remote session:

/* Grant alter session locally and remotely, if necessary */

SQL> connect system/manager
SQL> grant alter session to ;

SQL> connect system/manager@;
SQL> grant alter session to ;

/* create this procedure on the remote site in the schema above */

SQL> CREATE OR REPLACE PROCEDURE settrace
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set timed_statistics=true', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set events ''10046 trace name context
forever, level 12''', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/

/* This will activate the event in the remote session */

SQL> connect
SQL> exec settrace@
SQL> << execute statement to be traced here >>
SQL> exit

Each database (local and remote) will write a trace file to the user_dump_dest directory (10g and below).  In 11g, the files will be written to DIAGNOSTIC_DEST/diag/rdbms///trace, where DIAGNOSTIC_DEST is a database initialization parameter.

Since the tracing statements are implemented via ALTER SESSION, exiting the local session (which closes the connection made with the database link and thus exits the remote session) will terminate the tracing.

 

2)  The second method involves creating a login trigger for the schema on the remote database that runs the alter session commands whenever someone logs onto the schema.  An example is given below:

CREATE OR REPLACE TRIGGER logontrig AFTER logon ON database
begin
if ora_login_user = 'SCOTT' then
execute immediate
'Alter session set events ''10046 trace name context forever, level 12''';
end if;
end;

Alternative syntax for trigger:

CREATE OR REPLACE TRIGGER supptrace AFTER LOGON ON SCOTT.SCHEMA
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END;
/

The trace files will be written to the same locations as detailed above in method 1. 

The remote tracing will stop when the database link is closed or the local session disconnects.

Note that all sessions will be traced with this method, so it is advisable to disable or drop the logon trigger once the desired tracing is obtained.