SQL to get the redo volume historically

Redo volume is related to archive log volume, but not identical. Here is and SQL to get the actual cumulated redo sizes from the AWR. select * from ( SELECT TO_CHAR(begin_interval_time, 'yyyymmddhh24mi') tm, s2.instance_number, (s2.value - s1.value)/1024/1024 redo_size FROM dba_hist_sysstat s1 , dba_hist_sysstat s2, dba_hist_snapshot sn WHERE s1.stat_name = 'redo size' and s2.stat_name = 'redo …

How to estimate the parallel degree for historical SQL

In analysis of SQL stats,  execution times are cumulated for all parallel servers. This makes it difficult to derive the correct execution times from the AWR. Create some test data: The test script:   Output:   Though the SQL runtime was 1,27s, the accumulated elapsed time in v$sqlstats is 17,07s, due to the fact that all parallel …

Interesting problem with standby in windows 10 (solved)

The living room computer was set up to go to S3 sleep (non hybrid) after 15 minutes of inactivity. But still with having all settings correct, the computer would not go to sleep properly. Screen and audio would turn off, but the computer itself remains more or less active, even when entering standby through the …

Interesting reading about ASM disk space enhancement

To avoid ORA-15410, when adding disks of mixed sizes. http://blog.trivadis.com/b/robertbialek/archive/2014/10/28/asm-disk-size-check-with-oracle-12c-release-1-ora-15410.aspx

How to use booleans in SQL and avoid ORA-00904

Try this: select true from dual; ORA-00904: "TRUE": ungültiger Bezeichner The reason behind this behavior is that TRUE and FALSE are only available in PL/SQL, not in SQL. Booleans are simply not usable in SQL queries. How to work around this limitation? The SYS.DIUTIL package's BOOL_TO_INT() function provides a hint. Let's see. declare   n number;   v_true …

How to fix a broken windows update

I needed to reactivate a windows 7 computer, which had been sitting on the shelf for about 6 months. It refused to receive any updates. Here's how to update the system and re-enable windows updates: 1. ensure the system time is correct. If no, synchronize to internet time, e.g. net TIME \<computer name> /SET /YES Make sure …

How to include time zone and daylight saving settings in scheduler jobs

In case one needs to include time zone and daylight savings information in a job schedule, it must be supplied in the start_date parameter. The calendar arithmetic will then consider this information. BEGIN dbms_scheduler.create_job( job_name => 'some_job', job_type => 'PLSQL_BLOCK', job_action => 'begin null; end;', repeat_interval => 'freq=hourly; bymonthday=1,2,3; byhour=12,13,14;', start_date => trunc(systimestamp) at time …

Logon problems in OMS12C

Logging on as sysman to the OMS console fails, apparently due to incorrect username/password. Rebooting the OMS did not help. emoms.log shows the following: 2015-11-02 11:05:14,712 [[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR audit.AuditLogHandler auditLog.378 - Could not log the audit record java.sql.SQLException: ORA-14400: Eingefügter Partitionsschlüssel kann keiner Partition zugeordnet werden ORA-06512: in "SYSMAN.MGMT_AUDIT", Zeile …

How to work around ORA-01489

Aggregating a large number of objects into one string can lead to ORA-01489, due to the 4000 character limit of the listagg function. As an example, let's try to concatenate all user names in the database (referred to as "object"). select listagg(object, ',') within group (order by user_id) objects from ( select user_id, username object …

Real-Time SQL Monitoring – Statement Not Appearing

http://oracledoug.com/serendipity/index.php?/archives/1642-Real-Time-SQL-Monitoring-Statement-Not-Appearing.html