Rogerio Antonio

PT : AWR -  Workload Repository info EN : AWR -  Workload Repository info

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_

EN Por vezes e necessario fazer trace a sessao desde inicio, nessas situacoes e util usar um  logon trigger ex: baches de rpm PT Sometimes is useful to trace the session from the beginning, for that is useful to use a logon trigger eg RPM batches Code SQL>   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 tra

PT  : E a tal query que nao termina .. EN : And about that query that never ends ...

Versao Portuguesa In this post i'm sharing some queries I used to monitoring the messages processing in RIB. -- find all items queues SQL> select * from user_queues where queue_type = 'NORMAL_QUEUE' and name like '%ITEM%' --- find all items to process SQL> select * from ETITEMSTLFROMRIBTABLE -- queue subscribers SQL> select * from AQ$_ETITEMSTLFROMRIBTABLE_S -- queue historic SQL> select * from AQ$_ETITEMSTLFROMRIBTABLE_H where msgid = 'FE4C339218D830B1E0440024813309CD' --- find all tables/queue tables SQL> select * from dba_tables where table_name like '%ETITEMSTLFROMRIB%' Tesing the Integration - RMS > WMS An easy way to test the integration (RIB) , after add a new warehouse (WMS), is to choose an Item in RMS , update the description, commit the changes and validate if they are being cascaded to the WMS. After the validation put back the original description . Eg : Item : 101715654 -- RMS DB SQL> select  item, item_desc

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'''; e

Versao Portuguesa PT  :  Necessito mesmo  hard coded   na minha script ? EN :   Do I really need to  hard coded   my grant script ? set serveroutput on size 1000000; declare cursor c1 is ( select 'grant select on  wes.' || table_name  || ' to wes_ro'  cmd from dba_objects where owner like 'WES' and object_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','VIEW','MATERIALIZED VIEW','SEQUENCE') ); begin for i in c1 loop dbms_output.put_line(' ran : ' || i.cmd); execute immediate i.cmd ; end loop; end; / Thank you for reading, hope this post was helpful. Rogerio

PT  :  Necessito mesmo  hard coded   na minha script ? EN :   Do I really need to  hard coded   my grant script ? set serveroutput on size 1000000; declare cursor c1 is ( select 'grant select on  wes.' || table_name  || ' to wes_ro'  cmd from dba_objects where owner like 'wes' and object_type in ('table','table partition','table subpartition','view','materialized view','sequence') ); begin for i in c1 loop dbms_output.put_line(' ran : ' || i.cmd); execute immediate i.cmd ; end loop; end; /