Yesterday someone asked me how to trace a session that selects from a view in a remote database via a link. If they activated the trace on the local instance, they wouldn’t see the bulk of the work which was happening on the remote instance – just a bunch of SQL*Net Message from dblink waits.<\/p>\n
There are a number of ways to approach this. For example, using Active Session History (ASH)\u00a0on the remote database might give you the information you’re looking for without tracing, but only if you’re on 10g. I proposed a solution that’ll work on version 8.1 and later and tested it on 9.2.0.4<\/p>\n
I suggested using a login trigger on the remote database because it would ensure that all of the remote work was captured properly without needing to go into the remote database, quickly spot the session of interest and enable tracing on it. The requirement was further complicated by the fact that this job is probably using parallel query. I was initially sure that I’d find tons of web references to this, but was surprised to be wrong, so I thought I may as well stick a simple example on the blog. I’ve stripped out a few lines here and there to make it shorter, but hopefully the gist is clear.<\/p>\n
First I’ll go into the remote database and create the trigger. (Note – the only reason I forced parallel query on within the trigger was to simulate the questioner’s requirement more accurately)<\/p>\n
SQL> select name from v$database;Next I’ll check that there are no current TESTUSER sessions running on the remote database … <\/p>\n
SQL> select * from v$session where username = 'TESTUSER';and (at the shell prompt) check that there are no stray trace files kicking around that would confuse the end results.<\/p>\n
[oracle@ISP4400 udump]$ pwdNow that the remote instance is prepared, I’ll connect to the local instance – the one the job will run in – and show the different database name and database link details.<\/p>\n
SQL> connect \/ as sysdbaNow I’ll connect as TESTUSER and simulate application activity across the database link.<\/p>\n
SQL> connect testuser\/testuserHopefully the remote instance will have generated a few trace files for the activity.<\/p>\n
[oracle@ISP4400 bdump]$ ls -ltra<\/p>
[oracle@ISP4400 bdump]$ ls -ltra ..\/udump
Without doubt, the difficult part of this solution is limiting the collection scope. There might be all sorts of TESTUSER sessions on the remote database that I don’t<\/em> want to trace, but the trigger could be modified to limit the tracing by checking which machine the session is logging in from, to give just one example.<\/p>\nIn case people arrive here while searching for how to trace remote activity and bearing in mind this is only one approach,\u00a0all other suggestions are welcome.<\/p>\n","protected":false},"excerpt":{"rendered":"