Merge json files using Pandas

Quick demo for merging multiple json files using Pandas –

import pandas as pd
import glob
import json

file_list = glob.glob("*.json")
>>> file_list
['b.json', 'c.json', 'a.json']

Use enumerate to assign counter to files.

allFilesDict = {v:k for v, k in enumerate(file_list, 1)}
>>> allFilesDict
{1: 'b.json', 2: 'c.json', 3: 'a.json'}

Append the data into list –

>>> data = []

for k,v in allFilesDict.items():
    if 1  (more...)

DevOps for Oracle DBA

DevOps is natural evolution for Oracle database administrators or sysadmins of any kind. The key to remain relevant in the industry is to embrace DevOps these days and in near future.

The good news is that if you are an Oracle DBA, you already have the solid foundation. You have worked with the enterprise, world class database system and are aware of high availability, disaster recovery, performance optimization, and troubleshooting. Having said that, there is (more...)


For four years, this financial services company had been running Oracle Exadata on their Oracle databases. During that time, database administrators managed the Exadata infrastructure themselves but as the environment grew to six Exadata systems they struggled to keep up with the day-to-day care and maintenance of those systems. Having experienced multiple performance inconsistencies, the lean IT team of eight people required the infrastructure team to become involved. It was then that the company realized (more...)

Event incident

In a recent thread on oracle-l, Mikhail Velikikh has pointed me to an interesting but scarcely documented Oracle feature. The only documentation available is in oradebug:

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> oradebug doc event action incident
	- Create an Incident
incident( label 	  <string>[32])
This is not much of a documentation, but the usage is extremely interesting so I’ll give my (more...)

A new use for DML error logging

Many moons ago I did a short video on the DML error logging feature in Oracle. The feature has been around for many years now, and is a great tool for capturing errors during a large load without losing all of the rows that successfully loaded. You can watch that video below if you’re new to DML error logging.

But here is a possible new use case for DML error logging, even if you are (more...)

Optimizer Tricks 1

I’ve got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It’s worth taking note of these tricks when you spot one as a background knowledge of what’s possible makes it much easier to interpret and trouble-shoot from execution plans. I’ve labelled this one “#1” since I (more...)

Speaking at Oracle OpenWorld 2019

It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible). Details of my presentation are as follows: Conference: Oracle OpenWorld Session Type: Conference Session Session ID: CON1432 Session Title: […]

My OOW19 Schedule

If you don’t know why I like Oracle Open World, read this post. If you do, you know that I usually don’t go to many sessions as there are more important things to attend and do.

However, this year seems a bit different. It seems like there are many technical sessions and interesting people that I know (some personally, some from twitter and blogs), some I have never heard talking.

So I decided to write (more...)


This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.  In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a (more...)

AUSOUG Connect 2019 Conference Series

  AUSOUG will again be running their excellent CONNECT 2019 conferences series this year at the following great venues: Monday 14th October – Rendezvous Hotel In Melbourne Wednesday 16th October –  Mercure Hotel in Perth As usual, there’s a wonderful lineup of speakers from both Australia and overseas including: Connor McDonald Scott Wesley Guy Harrison […]

Join View

It’s strange how one thing leads to another when you’re trying to check some silly little detail. This morning I wanted to find a note I’d written about the merge command and “stable sets”, and got to a draft about updatable join views that I’d started in 2016 in response to a question on OTN (as it was at the time) and finally led to a model that I’d written in 2008 showing that (more...)

Video : Install Docker on Oracle Linux 7 (OL7)

Today’s video is a run through installing the Docker engine on Oracle Linux 7 (OL7).

You can get the commands mentioned in this video from the following article.

You can see my other Docker posts and builds here.

The star of today’s video is Robyn Sands, formerly of the Oracle Real World Performance Group, and now something to (more...)

DDL invalidates your SQL right ?

I stumbled upon this post by optimizer guru Nigel Bayliss last week, so please have a read of that first before proceeding. But I wanted to show a simple demo of how management of cursors continues to improve with each version of the database.

Throughout the many years and versions of using Oracle, a common mantra has been: if you perform DDL on a table, then any SQL cursors that reference that table will become (more...)

Why is it Important to do a Research Before Upgrading?

One of my client is on 12.1 and since the support for 12.1 has ended we started planning the upgrade of this database. During the research I did before the upgrade I found some things that were very important and affected our decisions and the upgrade process.

Choosing Version

Choosing version is the first step when thinking about upgrading. It’s easy to say “let’s upgrade to the latest one”, but it’s not always (more...)

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners)

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issue below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]

Birmingham Digital & DevOps Meetup : August 2019

Yesterday evening I went along to the Birmingham Digital & DevOps Meetup for the first time. It followed the usual meetup format of quick intro, talk, break, talk then home.

First up was Elton Stoneman from Docker with “Just What Is A “Service Mesh”, And If I Get One Will It Make Everything OK?” The session started by describing the problems associated with communication between the building blocks of a system, and how a (more...)

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions and NULLs

While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature

The Situation

Consider the following example:

SQL*Plus: Release - Production on  (more...)

New Subscription Option

I finally finalized new subscription and mail updates from GotoDBA website.

If you’d like to get updates, these are your options:

  • You can enter your email on the right under the “subscribe” option and you’ll be added to my mailing list. Please note that you will get a confirmation mail and only after you confirm you’ll start getting my updates. This is the recommended option as I’m going to add lots of great stuff to (more...)

Recovering ODA VMs From Lost ACFS Snapshots

This is a continuation of my previous post regarding dropped ACFS snapshots.  In this scenario, a user logged in to a virtualized ODA system and deleted the underlying ACFS snapshots for multiple virtual machines on the host.  Oracle advises how to back up and restore guest VMs on an ODA in MOS note #1633166.1.

The basic process to get a clean backup of the VM is to shut it down, take a (more...)

No more stale statistics in 19c

There is an odd contradiction that we all encounter for most databases, especially if they are predominantly used during the business day. Here is how that contradiction comes to be – it is in the way that we obtain and use optimizer  statistics on those databases. The contradiction runs like this:

  • To minimize service disruption, we gather statistics at a quiet time, for example, in the middle of the night
  • We then use those statistics (more...)