Skip to main content

Posts

DB - Monitor, Locks

E a tal query que nao termina And about that query that never ends ... -- --  General -- set lines 240 col message form a100 col opname form a50 select * from (  select  round(l.sofar/l.totalwork*100,2) as complete,  --l.*  l.sid,  --l.opname,  l.message,  l.start_time,  l.time_remaining/60 "minuts remaining",  l.elapsed_seconds  from  gv$session_longops l  where totalwork !=0 ) where complete < 100; -- --  RMAN Specific -- select sid, serial#,opname, context, sofar, totalwork,        round(sofar/totalwork*100,2) "%_complete" from v$session_longops where   lower(opname) like 'rman%'   and opname not like '%aggregate%'   and totalwork != 0   and sofar <> totalwork; select    to_char(start_time,'dd-mon-yy hh24:mi') "backup started",    opname,    sofar,    totalwork,    elapsed_seconds/60 "elapse (min)",    round(sofar/totalwork*100,2) "complete%" from v$session_longops where   lower(opname) like 'rman...
Post a Comment
Read more

RIB - JMS AQ

English Version Neste post partilho algumas queries que usei para monitorizar o processamento de mensagens no 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%' Testar Integracao - RMS > WMS Uma forma facil de testar a integracao (RIB) apos adicionar um novo warehouse (WMS), e actualizar um Item no RMS e validar se as alteracoes estao a ser transmitidas ao WMS, no exemplo seguinte mostro como fazer isso, e os tabelas/queues envolvidas. Item : 101715654 -- RMS DB SQL> select  item, item_desc  from item_master wher...
2 comments
Read more

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_...
Post a Comment
Read more