Shirts of OpenWorld

OpenWorld just isn’t OpenWorld without enjoying a bit of fun with one’s attire Smile


Img_0023a      Img_0026a

Img_0021a      Img_0025a

Img_0022a      Img_0024a

Oracle Midlands : Event #17

Don’t forget Oracle Midlands Event #17 next week!


I don’t know about you, but I would like to know what Oracle have been up to. There has been very little in the way of publicity from them recently… 🙂

If you live near, please show your support for the event and come along. It’s free thanks to the sponsorship by RedStackTech.



Oracle Midlands : Event #17 was first posted on September 22, 2016 at 7:41 am.
©2012 (more...)

The simple fix to date queries

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date).

SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_ix on t ( created );

Index created.

The problems start when (more...)

ERROR: SPFile in diskgroup does not match the specified spfile

Just a stupid error. Posting it so that someone else googling for the same thing can get a clue.

An ASM instance running with default parameters (no pfile, no spfile). Updated spfile for the instance with asmcmd spset command and bounced crs. After reboot also, it still wasn’t using spfile. Got puzzled and checked GPnP settings again. All looked good. Then in alert log came across this

ERROR: SPFile in diskgroup <> does not match  (more...)

Why Bother

This note comes to you prompted by “Noons” in a recent twitter exchange

In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno  asked me to blog about my claim that this non-difference is a good educational example on at least three different counts.

One thing I won’t count in the list of three is an actual demonstration (more...)

Do You Even TDM, (Test Data Management)?

Along with the deep learning I’ve been allowed to do about data virtualization, I’ve learned a great deal about Test Data Management.  Since doing so, I’ve started to do some informal surveys of the DBAs I run into and ask them, “How do you get data to your testers so that they can perform tests?”  “How do you deliver code to different environments?”


As a basic skill (more...)

Basicfile LOBS 5

At the end of the last installment we had seen a test case that caused Oracle to add a couple of redundant new extents to a LOB segment after one process deleted 3,000 LOBs and another four concurrent processes inserted 750 LOBs each a few minutes later (after the undo retention period had elapsed). To add confusion the LOBINDEX seemed to show that all the “reusable” chunks had been removed from the index which suggests (more...)

Ask Tom table about NOLOGGING and redo generation

I was googling for things related to NOLOGGING operations and found this useful post on the Ask Tom web site: url

There is a nice table in the post that shows when insert operations generate redo log activity. But it isn’t formatted very well so I thought I would format the table here so it lines up better.

Table Mode    Insert Mode     ArchiveLog           mode result
-----------   -------------   -----------------    -----------
LOGGING       APPEND          ARCHIVE LOG          redo generated

CRS-2615: Could not find server pool ‘smallpool’

RSS content

I received this error message when I was playing around with Extended Policy Framework in Oracle Clusterware 12c.

Policy-managed environment in Oracle clusterware 12c offers the capability to create policies to configure pools in accordance with business needs or application demand, so that pools provide the right service at the right time. Typically, administrators create multiple policies to reflect differences in requirements for the cluster based on business needs or demand, or based on (more...)

Partitioning an existing index

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I thought it would worth showing off some 12c features that would let you do this process with minimal disruption.

First we’ll create our table, with a standard primary key index

New graph: Average Active Sessions per minute

I am working on a production issue. I do not think that we have a database issue but I am graphing some performance metrics to make sure. I made a new graph in my PythonDBAGraphs program.


It shows the average number of active sessions for a given minute. It prompts you for start and stop date and time. It works best with a relatively small interval or the graph gets too busy. Red is sessions active (more...)

Securefile in 12c – part 2

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that.

We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition (more...)

Happy birthday to …. well … us :-)

Last year on September 1, the AskTom site was resurrected under Apex 5 with myself and Chris Saxon manning the fort to deliver as much value to the Oracle community as the esteemed previous custodian of the site did in the many years preceding us.

In the last year, we have

  • answered ~2,500 questions
  • taken and followed up on ~3000 reviews

It’s great fun and very rewarding working on the AskTom site.  We get (more...)

Parallel_index hint

Prompted by a recent OTN posting I’ve dug out from my library the following demonstration of an anomalty with the parallel_index() hint. This note is a warning about  how little we understand hints and what they’re supposed to mean, and how we can be caught out by an upgrade. We’ll start with a data set which, to match a comment made in the origina posting rather than being a necessity for the demonstration, has an (more...)

After OpenWorld…

Don’t forget that there’s lot of great content still to come your way throughout the rest of the year.

I’ll be part of the OTN Tour in Bangalore, Sydney and Brisbane.

And for a change of pace… I’ll be doing a keynote  at UKOUG !  (I’m the person in the list below who isn’t a senior director or vice president Smile)

Hope to see you at some or all of these events.



Started at Amazon! … want to join me?




(Disclamer: any opinions expressed here are fully my own and not representative of my employer)


photo by alvaroprieto  (cc 2.0)

Super excited to be working at Amazon on my passion which is performance data visualization and database monitoring. Suffice it to say this is the most excited I’ve been about work in my career and I’ve had ample opportunity to work on database performance in the past such as at (more...)

Presenting at Oracle Open World 2016


Just a short post to announce that I’ll be presenting an User Group Forum session. Thanks ODTUG!

SQLcl: A Modern Command Line Interface to the Oracle Database [UGF5641]

“In this session learn about the new Java-based command line interface that takes advantage of Oracle SQL Developer’s scripting engine. It delivers a modern command line interface that is backward compatible with SQL*Plus but also introduces new commands and features that have been missing for a long (more...)

Smarter DG Broker in Oracle 12c

In Oracle 11G, DG Broker needed to be told whether the standby database was physical or logical. The command to add database in 11G was something like this:



Oracle 12c is definitely smarter and can figure it out on its own. The “ADD DATABASE” syntax is simpler:

DGMGRL for 64-bit Windows: Version – 64bit Production

Copyright (more...)

How important is “up to date” to you ?

I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table.  (Long termers with Oracle may know that this is a relatively recent addition to Text indexes, which historically were updated with calls to CTXSYS.CTX_DDL.SYNC_INDEX, either on demand or via the scheduler).

I also said that there is “no such thing as a free (more...)

Synthetic Full Backup for Oracle

Synthetic full backup is a method for combining incremental backup into a full backup. It is not a new thing, many backup utilities can do that with file system backups. It is also well described:

However, there is only one backup suite that can do that with Oracle RDBMS: Commvault 11.

How is it done?  Well, Commvault is a very well documented tool, the documentation is here: