ON COMMIT Materialized View Causes ORA-02050 and ORA-02051

This example replicates a problem I discovered today. I tested it in an Oracle 11.2 database. First I created an empty table called tab1:

SQL> create table tab1
  2  (col1 varchar2(30),
  3   constraint con1 primary key (col1))
  4  /
Table created.

Then I set up a materialized view on the table. Note that it is refreshed on commit:

SQL> create materialized view log on tab1

Automation for DBA – Ansible part 1


In this post I would like to move forward with software provisioning and configuration. In my last post I covered a ways to build a "core VM" and now it's a time to add some software and configuration to it.

There is a couple of automation tools which can be used for that task like Puppet, Chef or Ansible to name a few. The latter one - Ansible - is my favorite, cause in (more...)


Here’s a live on from OTN – here are a couple of extracts from the problem statement:

We’re experiencing an issue where it seems that the query plan changes from day to day for a particular procedure that runs once a night.
It’s resulting in a performance variance of 10 second completion time vs 20 minutes (nothing in between).
It started occurring about 2 months ago and now it’s becoming more prevalent where the bad (more...)

SQL Profile and Plan Baseline Notes

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013

What is the difference between SQL Profiles and SQL Plan Baselines?
By Maria Colgan-Oracle on May 08, 2012

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011

3 OCT/10

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in (more...)

What To Do at OOW15 (Social & Serious)?

I’m going to OOW15 this year, my first Oracle Open World in 11 years I think. And despite the Prom Queen rejecting all my offerings, I will actually be sneaking in a presentation – which I am very happy about.

The European Oracle User Group (EOUG) get a few slots and two are being used on Sunday 25th, 13:30 – 15:15 for “More Than Another 12 on Oracle Database 12c” – 12 European experts (more...)

Oracle Database Developer Choice Awards : Spread the Love!

You’ve probably already heard about the Oracle Database Developer Choice Awards, Steven Feuerstein wrote about them here, but this is just a quick reminder to get your votes in.

This year there are five categories to vote for:

I am always a little nervous about “popularity contests”, but I would suggest you look through the finalists for each category and vote for someone if (more...)

How the log writer and foreground processes work together on commit.

(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)

After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)

You’re Not Allowed

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
Norton Juster, The Phantom Tollbooth 

Recently a thread in an Oracle forum I frequent asked the following question:

“… is there a way to restrict the login to database only from the application which we have made.”

That, of course, is not an easy question to answer as several possibilities emerge with none of them foolproof. Let’s look at those options.

The (more...)

Oracle Midlands Presentation

Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?

When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)

Master Notes

MoS has a number of “Master Note” documents which pop up from time to time while I’m checking for known problems or solutions; they get extended from time to time (and, conversely, link to some articles which are clearly no longer relevant to current versions). This is just a tidied up list of a few of the master notes that I’ve jotted down over time. The Data Dictionary overview appeared in my daily “Hot Topics” (more...)

Delphix Express installation

A couple of blog post ago I announced that there is now a free version of Delphix.  Here is a little more information about the installation of Delphix Express.

Delphix Express

Delphix Express is a free version of Delphix limited to 25 GB of managed storage and 1 vCPU. Delphix Express is set up for small projects and not optimized for high throughput nor performance testing.


Landshark is a set of 2 virtual machines (more...)

Oracle 12c RAC on Oracle Linux 6 and 7 using NFS

linux-tuxFollowing on from the last post, I’ve brought my NFS RAC stuff up to date also.

I noticed I had not done a RAC install using NFS on Oracle Linux 6, so I threw that in for good measure too. :)

Just as a little (more...)

Saving XML documents from an Oracle database

A colleague of mine asked about the several ways of saving XML content, I knew,…

PDB Saved state –

Prior to version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from, this can be done with PDB save state feature

SQL> show pdbs

---------- ----------------- ---------- ----------

Lets create a new PDB


Lock by reference

As you may know, not indexing foreign key can have a huge impact on lock behavior and hurt database scalability (Not only with Oracle).

But there is also some rules of thumbs (coming from Asktom) when non indexing the foreign key is considered as reasonable.

So, when do you NOT necessary need to index a foreign key ?

In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with (more...)

Come and Enjoy – OakTable World 2015

Yep, its free, its cool and a lot of fun. During Oracle OpenWorld 2015, the…

The Art of Being a Successful DBA- Paranoid DBA Best Practices

Ever look at a screen’s output and get that puckered feeling in the pit of your stomach? If you have been working in this profession for any amount of time, you know the feeling I’m talking about. The feeling that makes you think you would rather be living in Montana making woodcarvings at a roadside stand than being a DBA. I’ll be taking a somewhat lighthearted look at the perils of our profession and discuss (more...)

latch: cache buffers chains and rollback

I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event

load average: 258.52, 244.27, 226.15

select username,sql_id,event,count(*)  (more...)

Identifying database link usage

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that (more...)

Result Cache 2

Following on from my earlier posting of problems with temporary table and the PL/SQL result cache (a combination which the manuals warn you against) here’s another problem – again, to a large degree, self-inflicted.

Imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. Unfortunately the rules about calculating the appropriate exchange rate for any transaction are complex and you find you (more...)