Sokrates on Oracle

Archive for March, 2012

Learning foreign languages with Oracle SQL

Posted by Matthias Rogel on 23. March 2012

with y as
      add_months(date'2012-01-01', level-1) monn, 
      to_char(add_months(date'2012-01-01', level-1), 'MONTH') mon
   from dual
   connect by level<=12
   value as language, 
   to_char(y.monn, 'MONTH', q'|nls_date_language='|' || value || q'|'|') month, 
   to_char(y.monn, 'MON', q'|nls_date_language='|' || value || q'|'|') month_s
from v$nls_valid_values n, y
where n.parameter='LANGUAGE'
order by language, y.monn



Posted in Allgemein | Tagged: , | 8 Comments »

Snapshot Standby Monitoring

Posted by Matthias Rogel on 22. March 2012

Assume you have a Snapshot Standby configuration: during nighttime, it is used as standby, during daytime as test / development and you have set up DB-Links from primary to standby and vice versa with the appropriate grants.

select d.database_role, dgs.* from v$dataguard_status@primary dgs, v$database@primary d
union all
select d.database_role, dgs.* from v$dataguard_status@standby dgs, v$database@standby d
where message not like ‘RFS[%]: No standby redo logfiles created’
order by timestamp desc

is a great query to see simultaneously what happened one which node

Posted in Allgemein | Tagged: | Leave a Comment »