How To Trace A DBlink Connection (Local And Remote)

In many occasions we need to trace a session, possibly the most used in Oracle is 10046, this is a powerfull tool to see the execution times, but we usually use it on a single server.

Today I want to show a little trick to trace when we use a db link between two servers and generate this trace (can be used with others) in the two servers, the local and the remote. This can be usefull for example for snapshots between different databases.

In the remote server we create the following procedure within the user schema with which we are going to connect through the database link

 

REM *************************************
REM  You need create procedure privilege 
REM *************************************
CREATE OR REPLACE PROCEDURE set_on_trace
AS
BEGIN 

   EXECUTE IMMEDIATE 'alter session set TRACEFILE_IDENTIFIER = ''MY_TRACE_ANR'' '; 
   EXECUTE IMMEDIATE 'alter session set MAX_DUMP_FILE_SIZE=unlimited'; 
   EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'' '; 

END; 
/

On the local server, where we are going to execute the call to the database link

 
alter session set STATISTICS_LEVEL=ALL; 
alter session set events '10046 trace name context forever, level 12';

alter session set TRACEFILE_IDENTIFIER = 'MY_TRACE_ANR'; 
exec set_on_trace@my_database_link
select count (*) from siebel.orders@my_database_link;

 

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s