Top 5 Oracle Books on Amazon

For your edification and enjoyment, below are the current top 5 most popular Oracle DBA titles on

1. OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM by John Watson.

2. Oracle PL/SQL Programming by Steven Feuerstein

3.  Oracle SQL Tuning with Oracle SQLTXPLAIN by Stelios (more...)

EM12c – A Step Backwards?

Enterprise Manager Cloud Control 12c is mostly a better product than the 10g version we were using before.  But I am getting really frustrated with the Explain Plan functionality.

A big part of my job is performance tuning and monitoring running SQL.  And to do that you need (more...)

NOT NULL vs. Check Constraints

I found Jonathan Lewis's excellent article about "incorrectly" defining NOT NULL columns some time ago.  You can read his article at

Having read it, I did some investigation into our production databases and, lo and behold, found some examples of columns with NOT NULL check constraints but without the NOT NULL column definition.

Here's the query I used to find the 'guilty' columns:
select o.owner, o.object_type, o.object_name, t.column_name, co.search_condition, co.validated
 from dba_objects o, sys.col$ c, dba_tab_columns t, dba_cons_columns cc, dba_constraints co
 where o.owner = '&table_owner'
 and o.object_type  (more...)

Fisher-Yates (Knuth) Shuffle in PL/SQL

We were recently looking for a way to easily anonymize some customer data, specifically bank account numbers.

Part of our testing mechanism is to verify that a bank account and sort-code combination is valid and we retrieve the bank details for that combination (using a commercial account lookup service).  So we couldn't just replace the sort-code and/or account number with random numbers.

Conversely, replacing all account numbers with a handful of known, "safe" working values would lead to skewed data spreads, so that wouldn't work.

A better solution is to shuffle the existing data, moving the sort-code and account (more...)

Last Sunday

Our production databases are backed up with one of two schedules.  Most days our backups are sent offsite and retained for 3 months before being returned.  But on the last Sunday of each month, our backups are archived for a longer period of time.  Therefore the RMAN backups and Veritas catalogs need to be retained for up to 7 years!

Us Linux DBAs love cron for scheduling jobs. These fancy software schedulers with their dependencies and clever logic have never really replaced a really dense, difficult to read crontab file. And that's just the way we like (more...)

Cold Backup RMAN Fun and Games

Here's an RMAN oddity.  If anyone could explain it, I'd be grateful.

We were moving a database to a new server.  Same OS, same version of Oracle (  The database is in Archivelog mode, but to keep things easy, we decided to do an offline, consistent backup.

So, we shutdown the database, then mounted it and performed an RMAN backup to a flash_recovery_area on a shared NFS filesystem:

backup device type disk database plus archivelog include current controlfile;
The backup worked fine. So we logged on to the new server, made sure that we could (more...)

When is DDL not DDL?

If you ever thought that the LAST_DDL_TIME on a table only tells you the last time that DDL was performed directly upon that table, then think again.

We recently noticed some tables which appeared to have been recently changed, yet we couldn't find any related DDL changes.

Then we remembered that we regularly rebuild a handful of indexes (once a week, on Sunday morning) for performance/plan-stability reasons.

Could it be that rebuilding an index resets the LAST_DDL_TIME for the index's table?

Let's find out.

SQL> create table poo (x date);

Table created.

SQL> create index poodex on poo(x);

Index created.


Table Comparison Signature

We've been working on a data archiving project to move old data from the production schema in one database into an archive schema in a different tablespace, on a remote server.

We copy data from a production table (let's call it PROD.FINANCE_TAB into a pre-existing table in an archive schema (e.g. ARCH.FINANCE_TAB).  The fact that they are in a different database is actually irrelevant to the problem at hand.

If  PROD.FINANCE_TAB were to change structure (due to an added column or a datatype change), the archive transaction may fail.  So we need to (more...)

RMAN Duplicate Gotchas – Part 2: Standby DB Restores and Redo Log Group

In my previous post I discussed identifying which media you would need to use when performing a restore in order to duplicate a database via RMAN.

In this post, I'll discuss a couple of other issues that you may come across; although, admittedly, they are fairly uncommon.

Standby DB Restores

Like a lot of other sites we maintain a physical standby database, managed using Dataguard, ready to use as a failover instance, in case of problems with the main RAC database. This database is the source for all backups, to avoid overloading the production servers. This leads to a small (more...)

RMAN Duplicate Gotchas – Part 1: Identifying the Media

I've recently been doing quite a few RMAN Duplicate Database jobs, in order to create dev and test systems as copies of our production server.

The production database is a 2-node RAC cluster, but each of our test systems are single-instance databases. This causes one or two odd issues which aren't always obvious until you've hit them at least once.

In this post I'll discuss the first tricky issue which I came across: Identifying the Media.

At my current job (as in most IT departments), each day the most recent backup tapes are removed from the tape robot and sent (more...)

Available January

I've just discovered that my current contract, with a healthcare company in Hampshire, will not be extended beyond the end of the year. So, in the current economic climate, I've decided to start a blog in order to drum up more business.

My primary skills are in troubleshooting and tuning Oracle systems, from entire databases to individual SQL queries.

If you have an application running on an Oracle database and you need someone to investigate an issue on that system, get in touch.

"My name is Peter Moore. I am a professionally qualified and highly experienced database administrator with extensive (more...)