Recently I needed to trace queries from dblink, and I decided to make it more convenient, without the trigger.
These are the three simple steps:
- Creating new service
- Turning on tracing on this service
- Editing tnsnames and adding dblink for this service if needed
Now we can enable tracing anything just connecting to this service.
Below code for sql*plus:
begin DBMS_SERVICE.CREATE_SERVICE('db11203trace','db11203trace'); end; / column new_value new_value new_value; select p.value||',db11203trace' new_value from v$parameter p where p.name like 'service_names'; alter system set service_names='&new_value'; exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'db11203trace',waits => true,binds => true);