However we ran into one problem the other week. I noticed that the test3 database was still in physical standby (more...)
ERROR at line 1:
ORA-27373: unknown or illegal event source queue
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 314
ORA-06512: at line 2
The CREATE_JOB statement was:
job_name => 'foo.bar_q_job',
job_type => 'PLSQL_BLOCK',
connect auxiliary /;
duplicate database to testdb
backup location '$BACKUPDIR'
One important difference between the demo database and this new test database is that the original demo database (more...)
As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC (more...)
A comparison of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.
This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.
I think it’s a good example of (more...)
SQL> alter table p_objects
2 add partition p201410
3 values less than (to_date('2014/11/01','yyyy/mm/dd'))
5 subpartition p201410_spdts values ('DTS')
There seems to me to be a relatively simple choice.
Either you except that the Oracle Optimizer has a wealth of complicated strategies and, in this complex effort to get the best executions it can, will inevitably either get it wrong sometimes (or take some extra executions to realise it’s wrong).
Or you stick your head in the sand and raise a bug for every unexpected poor execution or plan flip.
But let’s say that (more...)
In case you are planning to perform any upgrades with DBUA, double-check that the init.ora parameters after the upgrade. In a production upgrade at a client site, dbua silently changed init.ora parameter NLS_TERRITORY from GERMANY to AMERICA. It was only noticed when after the upgrade, decimal and grouping number characters were swapped, leading to application problem.
Support had to admit that it is unpublished bug 16538186 and that there is NO documentation of (more...)
However when I went to start things up, ASM wasn't starting up, giving me these errors:
ORA-04031: unable to allocate 32 bytes of shared (more...)
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.
Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba
SQL*Plus: Release 188.8.131.52.0 Production on Tue Jul 1 09:34:18 2014
As usual the latest version can be downloaded here.
This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.
Here are the notes from the change log:
- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)
A quick fly through the options for conditional uniqueness.
Requirement #1: I want uniqueness on a column but only under certain conditions.
For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.
create table t1 (col1 number not null ,col2 varchar2(24) not null ,is_active number(1) not null ,constraint pk_t1 primary key (col1) ,constraint (more...)
According to http://en.wikipedia.org/wiki/Perfect_storm:
A “perfect storm” is an expression that describes an event where a rare combination of circumstances will aggravate a situation drastically.
The other day we had a bit of a panic when AUD$ space grew by 45GB in 15 minutes and set off some alerts.
The audit_trail parameter was set to DB, EXTENDED.
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text (more...)
We first applied the April 2014 PSU on our staging & development database hosts (more...)