OR - Trace session/batches

English Version

Por vezes e necessario fazer trace a sessao desde inicio, nessas situacoes e util usar um  logon trigger ex: baches de Oracle Retail / RPM / RMS

Em seguida partilho tres logon triggers , que podem ser uteis nessas circunstancias, o primeiro usando o hostname do cliente.

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.

Usando Service Name

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 sessao (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;

Obrigado pela leitura, espero que este post o tenha ajudado.
Rogerio