Who's online

There are currently 0 users and 22 guests online.

Recent comments


Test Data Management (TDM) – your downfall or triumph?

#222222;">A recent post on LinkedIn had this image:


The problem is as old as development. The tester finds a bug but the developer can’t reproduce the bug so closes it as unreproducible.

… but how else can testing show development the bug? Should the developer come over to the testers desk and work on the testers systems while the tester waits?

ASH art on Twitter profiles

What fun to see Top Activity aka ASH artwork on twitter profiles:

Screen Shot 2015-12-28 at 1.33.45 PM

Screen Shot 2015-12-28 at 1.32.59 PM

Merry Christmas

To the tune of “Let It Snow”

Well, the response of the app is frightful,
and users are getting spiteful.
They claim the database is slow,
“Make it go”, “Make it go”, “Make it go”

It doesn’t show signs of locking,
Yet the performance it still quite shocking,
The CPU is right down low,
but its slow, but its slow, but its slow.

When we finally trace the load,
The problem is rapidly found,
If you write lots of crappy code,
Then your app crumples to the ground

Now we write the application better,
The DB is no longer fettered,
So as long as we write good code,
It wont be slow, wont be slow, wont be slow.

Enterprise Manager 13c Upgrade

My goodness me it’s been a long time since I updated here. As it’s nearly the season for New Years resolutions I thought I’d make one early and start updating again. This post is inspired by the release on Friday of Oracle Enterprise Manager 13c. Already there’s a nicely screenshotted How to Upgrade article by @gokhanatil . Step […]

How big is my offline datafile ?

“Strange” things happen when you take a tablespace or its datafile(s) offline.

SQL> create tablespace TS datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' size 20m;

Tablespace created.

SQL> alter tablespace TS offline;

Tablespace altered.

SQL> select bytes from dba_data_files where file_name = 'C:\ORACLE\ORADATA\NP12\TS.DBF';


We lose access to the file size. So how can you tell how large that file is, without jumping into the OS or bringing the tablespace online again ?

We can treat the file as a blob.

How do I stop the use of TRUNCATE

Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.  So often we’d like to bar the use of it.  Obviously, to truncate a table in another schema you need DROP ANY TABLE which one would hope very very very few people, aka none :-) would be granted, so really this is all about connections as the owner of a table.

Ideally, the solution is part of the application implementation, namely, that people never connect as the schema owner – that’s just good security practice for applications even without considering truncate.


A recent question on the OTN database forum asked:

I have a table with a,b,c,d,e,f,g,h,i,j,k. columns and I have an index on (a,b) columns. There is a sql statement now with “where a= ?” and we are wondering if it could also be good to add a single index on just (a).

Does it help at all? Does it help in some cases?

This is one of those questions where the answer for a perfectly designed and managed system could easily contradict the pragmatic answer for a live system in its current state. That may mean you have to do the wrong thing in the short term while working (possibly very slowly) towards the right thing.  I gave the following (slightly edited) answer on the forum:

The art of describing a problem

Now that I’ve been answering AskTom questions for a while, there is obviously a huge amount of variety in the topics and the problems that people are encountering.  And it always feels good when I can help those people.  Chris and I don’t always manage to solve everyone’s problem (sometimes we just have to say “Get in touch with Support”) but I think we’ve got a pretty good hit ratio Smile  [For those unaware, we don’t publish every single question/answer – we answer a lot more than what you just see on the page]

Highlights from UKOUG

As my train whizzes past Conventry, *and* has wifi, it seems a good time to reflect on this years conference.

I’ve always loved this conference.  I lived in the UK from 1998 to 2003, and a RDBMS SIG meeting was my first foray into presenting on an Oracle topic.  At the time, I thought I’d be relocating back to Australia shortly thereafter, so it granted me plenty of leeway for profanity, props and being loose with what I said :-)

But that set the ball rolling on my love of sharing information with an audience (and whats more, an audience that had a genuine keenness to learn new skills).  I was the fortunate enough for a few years to be funded by the Ace Director program to come to the Birmingham conference.  But ultimately that came to an end, and the several thousand dollar cost of coming to the UK from Perth meant that I had to give up the conference.

Five Hints

This is the content of a “whitepaper” I wrote for my presentation “Five Hints for Optimising SQL” at the recent DOAG conference.


Adding hints to production code is a practice to be avoided if possible, though it’s easy to make the case for emergency patching, and hinting is also useful as the basis of a method of generating SQL Plan Baselines. However, notwithstanding (and sometimes because of) the continuing enhancements to the optimizer, there are cases where the only sensible option for dealing with a problem statement is to constrain the broad brush strategy that the optimizer can take in a way that allows it to find a reasonable execution plan in a reasonable time.

This note describes in some detail the use and effects of five of the “classic” hints that I believe are reasonable strategic options to redirect the optimizer when it doesn’t choose a path that you consider to be the most appropriate choice.