OR – Trace session/batches

Versao Portuguesa

Sometimes is useful to trace the session from the beginning, for that is useful to use a logon trigger eg RPM batches

I'm sharing three logon trigger examples to help in that situations, the first one using the client hostname, if you know it.

 SQL> drop trigger sys.set_trace_rpm;

SQL> create or replace trigger sys.set_trace_rpm
after logon on database
when (user in ('RPM_OAS'))
declare
retail_module varchar(200);
begin

select module into  retail_module from gv$session
where
sid =  upper(sys_context('userenv','sid'))
and (osuser is null and machine like 'rpm_app_server%');

execute immediate 'alter session set statistics_level=all';
 execute immediate 'alter session set max_dump_file_size=unlimited';
 execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 execute immediate 'alter session set tracefile_identifier=''RA_RPM''';

exception
when no_data_found then
    null;
when others then
    null;
end;
/
Trigger created.

SQL>  drop trigger sys.set_trace_rpm;
Trigger dropped.

This one using database services.

SQL> create or replace trigger sys.set_trace_rpm
after logon on database
when (user in ('RPM_WEBLOGIC'))
declare
  retail_module varchar(200);
begin

 select module into  retail_module from gv$session
 where sid =  upper(sys_context('userenv','sid')) and  SERVICE_NAME like 'RPM14_SERVICE';

 execute immediate 'alter session set statistics_level=all';
 execute immediate 'alter session set max_dump_file_size=unlimited';
 execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 execute immediate 'alter session set tracefile_identifier=''RPM''';
  
 exception
 when no_data_found then
    null;
 when others then
    null;

end;
/
Trigger created.

SQL> select OWNER,TRIGGER_NAME,STATUS from dba_triggers  where trigger_name like upper('set_trace_rpm');

OWNER                          TRIGGER_NAME                   STATUS
------------------------------ ------------------------------ --------
SYS                            SET_TRACE_RPM                  ENABLED


SQL> drop trigger sys.set_trace_rpm;
Trigger dropped.

RMS - Trace Session (Logon Trigger)

drop  trigger sys.set_trace_orrmsb;

create or replace trigger sys.set_trace_orrmsb
after logon on database
when (user in ('BATCH_USER'))
declare
retail_module varchar(200);
begin

select module into  retail_module from gv$session
where
sid =  upper(SYS_CONTEXT('USERENV','SID')) and ((upper(module) like '%ProC_Name%' ));

execute immediate 'alter session set statistics_level=ALL';
execute immediate 'alter session set max_dump_file_size=UNLIMITED';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
execute immediate 'alter session set tracefile_identifier=''DBA_TRACE''';

exception
when no_data_found then
null;
when others then
null;

end;
/

drop  trigger sys.set_trace_orrmsb;

Thank you for reading, hope this post was helpful.
Rogerio