Database Comparisons Using AWR Warehouse Part 1- ADDM Comparison

A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter.  Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another.


There is a  lot of planning that must go into a project to migrate a database (more...)

RI Locks

RI = Referential Integrity: also known informally as parent/child integrity, and primary (or unique) key/foreign key checking.

I’m on a bit of a roll with things that I must have explained dozens or even hundreds of times in different environments without ever formally explaining them on my blog. Here’s a blog item I could have done with to repsone to  a question that came up on the OTN database forum over the weekend.

What happens (more...)

A week in the cloud…

cloudIt’s been just over a week since I moved my website over to “the cloud”. Well, an EC2 virtual machine on Amazon Web Services, so it’s Infrastructure as a Service (IaaS), not really what I consider the cloud. 🙂

Overall I’m really happy with the way it’s gone so far. As I mentioned in a previous post, I’ve gone for a VM that is significantly lower spec than the dedicated server I had before. I’ve been (more...)

Fixing broken links to Oracle documentation… Again…

Broken-LinkWith my recent website move I thought I better check for broken links, in case I had screwed anything up during the transfer. The last few times I’ve done this I’ve used SiteCrawl, which seems to do a decent job.

After the scan had finished I looked through the results and had a couple of broken internal links and 171 broken external links. Can you guess where the vast majority of the broken external links (more...)

Oracle 12c Caching and In Memory Databases

A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.

One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the (more...)

How to Share OEM Named Credentials

Named Credentials

Named credentials (NC) are an excellent way to support the work performed by OEM users/administrators without requiring them to actually know key passwords. Named credentials also provide a single location to update when the passwords change.

The Problem

Named credentials can only be shared with individual users/administrators inside OEM. You can’t grant the privilege to an EM role and expect the privilege to propagate the role members like you can with a similar database (more...)

Using PL/SQL to run the SQL tuning Advisor in 11gR2

Using PL/SQL to run the SQL tuning Advisor in 11gR2

In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.

The approach is simple we run a single SQL (more...)

Moving Sideways

The past two years have seen a lot of change in my life :-
  • I moved to Singapore thinking it would be for a year or two at least and is now looking like it will be longer.
  • I started my first role as a permanent employee of someone else's organisation for the first time in around 22 years. (This was the one that most blew away those who know me well!)
  • I went (more...)

Comparing Common Queries Between Test and Production

The developers complained that their test database was so much slower than production that they could not use it to really test whether their batch processes would run fast enough when migrated to production. They did not give me any particular queries to check. Instead they said that the system was generally too slow. So, I went through a process to find SQL statements that they had run in test and that normally run in production (more...)

E-rows / A-rows

A recent post on the OTN database forum reminded me how easy it is to forget to keep repeating a piece of information after the first couple of hundred times you’ve explained it. No matter how “intuitively obvious” it is for one person, it’s new to someone else.

Here’s an execution plan that raised the question that prompted this note – it comes from calling dbms_xplan.display_cursor() with the ‘allstats last’ format option after enabling (more...)

Why You Should Never Use MongoDB « Sarah Mei

An interesting article from Sarah – much good advice there!

Source: Why You Should Never Use MongoDB « Sarah Mei

UKOUG Tech16 Call For Papers!

It’s that time of year again. The UKOUG Tech16 conference call for papers went out today.

You want to learn a subject really well? Better than you currently know it? Teach someone about it!

You want to help people avoid the potholes and problems that you hit with your implementation? Tell us about that and we’ll love you!

Hi! My name is Neil, and once I was scared to get up in front of people (more...)

EM13c- Applying System Patches with the OMS Patcher

The OMS Patcher is a newer patching mechanism for the OMS specifically, (I know, the name kind of gave it away…)  Although there are a number of similarities to Oracle’s infamous OPatch, I’ve been spending a lot of time on OTN’s support forums and via email, assisting folks as they apply the first system patch to  Admit it, we know how much you (more...)


The second edition of Cary Millsap‘s MASTERING ORACLE TRACE DATA (MOTD) is finally available. You can order it through I had the pleasure not only to review MOTD while Cary was working on it, but also to write a foreword that summarizes what I think about the book. So, if you are asking yourself whether you should buy MOTD, here is my opinion/foreword…

In late 1999, through an Oracle Support note, I (more...)

Star Transformation in 11gR2

Star Transformation in 11gR2

 In the article we will review the prerequisites for the CBO to perform a star query transformation. We will use the SH samples schema. We will look at two cases with and without a bit map join index.

  1. Star transformation without a bitmap join index.
  2. Star transformation with a bitmap join index.

The prerequisites are as follows.


  1. Make sure that the primary and foreign keys are enabled.





A colleague asked if there was a way to do column level dependency tracking recently. He wanted to know for a given view, which tables and the columns on those tables, it was dependent upon, without, of course, reading through the code.

I was vaguely aware that since 11gR1 Oracle has been tracking fine grained (column) dependencies, but couldn’t find a way of seeing the details stored, until I found this interesting article from Rob (more...)

SQL Riddle: Find the Sum of ASCII Codes of Employee Names (solution)

Zahar Hilkevich (from the blog – – cool blog, you should check it out) posted a riddle on Facebook.
The question was:

“For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.”

----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299

Oracle backups in Azure

This post is all about how you can back up Oracle in Azure, well 'all about' is an exaggeration - this is what i have set up for our current Oracle backups in Azure. There is of course more than one way to do this - the solution below works for us but may not work for you.

Feedback on this would be great but please make it constructive ("this is shit" is not constructive (more...)

Who Can We Trust?

Searching google for answers is something we all do with every question or request for information we have. I sometimes wonder how much of this data is simply wrong. This can be because people write stuff they don’t know about, people are sharing false information (that was published by others by accident or on purpose), […]

Blue Medora’s Brian Williams Blogs About Customer Monitoring Templates

Monitoring templates are an essential feature to a basic Enterprise Manager environment, ensuring consistent monitoring across groups and target types.  There’s an incredibly vast group of experts in the EM community and to demonstrate this, Brian Williams from Blue Medora, a valuable partner of Oracle’s in the Enterprise Manager space, is going to provide a guest blog post on how simple and efficiently you can monitor even (more...)