Multi-table insert

An interesting question came through on AskTom recently.  The requirement was to perform a single pass through a source table, and load the data into three target tables.

Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this requirement.  Each of the three target tables may already contain some, none or all of the rows from the source table.  Hence the requirement was to “fill (more...)

How to Create Storage Containers on the Oracle Cloud

When creating a Database Cloud Service, you have an option to select “cloud storage” as a backup destination. In this case, the database backups will be stored in “storage containers”. As I see, lots of Oracle Cloud users have problem with setting up storage containers as backup destination.

It’s possible to create a storage container before you create the database service or you can create it while provisioning the DB service. Whichever method you chose, (more...)

12 Days of #Cloud: Apply Patch to #DBaaS Instance

12 Days of #Cloud: Day 9 I have a 12.1. Database Cloud Instance, which comes with several patches including July 2016 PSU. The dbaascli cloud tool can be used to query and apply patches. Before we use dbaascli, let’s check what the Cloud interface shows. Choose the DBaaS instance and view details. The “Administration” tile shows […]

Using Read Only Tables

While visiting a customer, we had a conversation about the correct way to stop users from writing to code tables. The customer described his ancient logic: when he wanted to move table to a read only state, he removed the write (insert/update/delete) permissions from all of his users. That was good enough for him for years – since he started using this method way back in Oracle 8. All was well, util this week he (more...)

GNW05 – Extending Databases with Hadoop video (plus GNW06 dates)

In case you missed this webinar, here’s a 1.5h holiday video about how Gluent “turbocharges” your databases with the power of Hadoop – all this without rewriting your applications :-)

Also, you can already sign up for the next webinar here:

  • GNW06 – Modernizing Enterprise Data Architecture with Gluent, Cloud and Hadoop
  • January 17 @ 12:00pm-1:00pm CST
  • Register here.

See you soon!


NB! If you want to move to the "New World" - offload (more...)

Expanded ACE Recognition at RMOUG Training Days

The only thing that remains constant is change.   ~Heralitus


This often is more apparent in technology than anywhere else.  Even with a group of Oracle evangelists and enthusiasts like the ACE program, which offers the opportunity for peers to recognize those that contribute to the community and help make it a better place.

I’ve [often quietly] assisted many around me to find their path into the ACE (more...)

Books I’ve read recently…

For those that don’t know, I came to reading Fiction rather late. In September of 2006, at the age of about 37 I decided to start reading. Up until that point I had pretty much avoided reading wherever possible for most of my life. I did what I had to for my degree, PhD and job, but I was a minimum effort reader. 🙂 The first series of books I read was The Vampire Chronicles and I (more...) Interviews (German)

In den letzten Wochen sind zwei Interviews veröffentlicht worden, die die DOAG mit mir im Rahmen der jährlichen DOAG Konferenz in Nürnberg durchgeführt hat.

Das erste stammt noch von der DOAG Konferenz 2015 und bezieht sich auf meinen damaligen Vortrag über die neuen Parallel Execution Features von Oracle 12c: Interview 2015

Das zweite ist von der diesjährigen DOAG Konferenz und bezieht sich auf meine Performance-Tests der Oracle Database Cloud und dem dazugehörigen Vortrag:


“U” entries in MLOG$

Recently in a discussion with colleague, on what entries do MLOG$ have, the following was agreed to :-

1. “I” for insert
2. “D” for delete
3. “D” + “I” for update.

Along with the above entries, you might notice “U” also. The “U” entry is for update. But then, on what condition do we get “U” and when do we get “D” and “I” entries.

I have used “EMP” table as master table in (more...)

PL/SQL Objects for JSON in Oracle 12cR2

I’ve been playing around with some more of the new JSON features in Oracle Database 12c Release 2 (12.2).

The first thing I tried was the new PL/SQL support for the JSON functions and conditions that were introduced for SQL in 12.1. That was all pretty obvious.

Next I moved on to the new PL/SQL objects for JSON. These are essentially a replacement for APEX_JSON as far as generation and parsing of JSON data (more...)

Fixing Dataguard Wrong File Location Problem

When we add a new datafile to the primary environment of a dataguard setup, we need to add the datafile to the standby environment as well if we want the manged recover to continue working. We can do that manually if we want – but fortunately for us Oracle comes with a built in feature for automatically creating the file in the standby as well. All we need to do to set it up is (more...)

Enabling Flashback – Be Cautions

Enabling flashback on database can take minutes and cause multiple database wait events degrading database performance.

On a quite busy system, during the low peak hours, I tried to enable flashback on version database and multiple sessions waited on

1. buffer exterminate
2. enq: SQ – contention
3. latch: cbc contention
4. SGA: allocation forcing component growth

PRAKANAN_DBA@:1> show parameter sga_target
NAME                           TYPE        VALUE
------------------------------ -----------  (more...)

Happy Holidays

So it’s Christmas morning here in the UK.

I’m pretty tired after last night’s events. The night before I heard something in my ceiling, which I was hoping was mice, but suspected might be this.

Last night I put a humane trap in the attic and in the evening heard some noise as the trap was rocking back and forth. I took it outside to a hedge about 100 yards away, as suggested by a couple (more...)

APEX 5.1 Installations and Upgrades

APEX 5.1 was released for download a few days ago. I tried doing an upgrade against an installation on a VM at home and it worked fine, which was hardly surprising. 🙂

Officially I’m on holiday, but I figured I would upgrade all our Dev/Test installations while everything is quiet. Major version upgrades, changes in either of the first two numbers, require a full installation. There was no major difference between this and what I (more...)

interconnect fragmentation kills the cluster

On a particular Oracle 2 node cluster (12.1) we faced random instances failing. Servicerequests at Oracle were open with limited result, as it was quite random and we could not link it to any trigger.
As it looked somehow like a communication problem between the 2 nodes, network team has checked the switches involved - without any outcome.
Even crashing instances were a problem already, it get worse one day when one node rebooted (more...)

12 Days of #Cloud: Paused

Having trouble with the trial subscription especially around storage cloud service. Will come back as soon as I get help from Oracle… Happy Holidays!          

Primary Storage, Snapshots, Databases, Backup, and Archival.

Data in the enterprise comes in many forms. Simple flat files, transactional databases, scratch files, complex binary blobs, encrypted files, and whole block devices, and filesystem metadata. Simple flat files, such as documents, images, application and operating system files are by far the easiest to manage. These files can simply be scanned for access time to be sorted and managed for backup and archival. Some systems can even transparently symlink these files to other locations (more...)

Dealing with a global increase in CPU usage

Imagine the following situation: you are supporting an application with many different components and a busy release cycle. One a Monday morning you find that quite a few processes in the database now run slower. Very soon, you find out that the slowdown is due to increased CPU time, but where to move from there? There is no evidence that CPU is too stressed, causing CPU queuing. You cannot isolate the problem to any specific PL/SQL (more...)

Friday Philosophy – Genial Greetings & Festive Fun

For many of us, today will be the last working day before the Christmas Break (unless you live in one of the many places that don’t celebrate Christmas, in which case ignore this post). So Merry Christmas everyone and Happy New Year!

My brother's "Christmas Card" to us this year

My brother’s “Christmas Card” to us this year

These good wishes are especially directed at those who have to be on call or actually working over the holiday period. It’s one of those (more...)

Inserting data in SQL*Plus correctly

When inserting data into the database, it is occasionally forgotten (especially by English-speakers) that we need to take steps to ensure we are inserting data correctly and without unexpected character translation.

For example, in SQL*Plus we need to ensure we set the NLS_LANG environment variable to the correct setting for our database before we initiate SQL*Plus.

Here’s a quick example showing what can go wrong:

[oracle@ORA122 ~]$ echo $NLS_LANG

[oracle@ORA122 ~]$ sqlplus neil/neil
SQL*Plus: Release  (more...)