Oracle – Pinning table data in the Buffer Cache

As I write, Euro 2016 is in full swing.
England have managed to get out of the Group Stage this time, finishing second to the mighty…er…Wales.
Fortunately Deb hasn’t mentioned this…much.

In order to escape the Welsh nationalism that is currently rampant in our house, let’s try something completely unrelated – a tale of Gothic Horror set in an Oracle Database…

It was a dark stormy night. Well, it was dark and there was a persistent drizzle. It was Britain in summertime.
Sitting at his desk, listening to The Sisters of Mercy ( required to compensate for the lack of a thunderstorm and to maintain the Gothic quotient) Frank N Stein was struck by a sudden inspiration.
“I know”, he thought, “I’ll write some code to cache my Reference Data Tables in a PL/SQL array. I’ll declare the array as a package header variable so that the data is available for the entire session. That should cut down on the amount of Physical IO my application needs to do !”

Quite a lot of code later, Frank’s creation lurched off toward Production.
The outcome wasn’t quite what Frank had anticipated. The code that he had created was quite complex and hard to maintain. It was also not particularly quick.
In short, Frank’s caching framework was a bit of a monster.

In case you’re wondering, no, this is not in any way autobiographical. I am not Frank (although I will confess to owning a Sisters of Mercy album).
I am, in fact, one of the unfortunates who had to support this application several years later.

OK, it’s almost certain that none of the developers who spawned this particular delight were named after a fictional mad doctor…although maybe they should have been.

In order to prevent others from suffering from a similar misapplication of creative genius, what I’m going to look at here is :

  • How Oracle caches table data in Memory
  • How to work out what tables are in the cache
  • Ways in which you can “pin” tables in the cache (if you really need to)

Fortunately, Oracle memory management is fairly robust so there will be no mention of leeks

Continue reading

Oracle Database Diagnostic and Tuning Packs – exactly what are you not licensed for ?

It’s that time of year. The expense of Christmas is becoming apparent and January payday has seemed to be forever in arriving.
“…and I need a crown !”, said Deb.
This caused me to pause for a moment. Was my better half getting delusions of granduer ?
Further, delicate enquiries revealed that it was merely a dental crown to which she was referring.
Not that it seems to make much difference financially. You could probably buy a fairly substantial piece of regal headgear for what the dentist was asking.

On the plus side, Queen Deb’s costume for the next instalment of the Licensing Epic doesn’t require such accoutriments…although a big pair of headphones and lots of hairspray may be in order. Yes, I’m still on my Star Wars themed odyssey through Oracle Database Licensing.

In the previous posts, I’ve already covered :

Now, it’s time to get to grips with the licensing minefield that are the Diagnostic and Tuning Packs.
Queue the orchestra….

Episode 2 – Attack of the Diagnostic and Tuning Packs

Confusion is rife in the Data Centre. The Geeki have found that the incredibly useful AWR and ASH utilities are in fact secret members of The Diagnostic Pack.
Fearing the presence of the Dark Side, they must now re-enter the realms of the mysterious Oracle Database License to

  • Determine which features are part of these packs
  • Work out exactly what constitutes usage of these packs
  • find out which database objects that are part of these packs

With this information, at least they will know which objects they must avoid if they are not to have to pay substanital additonal licenses…


This code has been written and tested on Oracle Database 11gR2 Express Edition.
The licensing information I’ve referenced is for Oracle Database 11gR2.
I’m fairly sure it all works as expected. However, as you undoubtedly know, you shouldn’t take my word for it.
Before you go playing around with this on any production environment, please make sure it does what I think it does.
Of course, if you do find any issues, I’d be great if you could put a comment on here so that I can correct any issues…and also to give a pointer to anyone else looking at this post.
Yes, I know the standard disclaimer about “similarity to events or persons living or dead” always goes at the end of the film, but I thought it best to put it at the start.
Incidentally, have you ever wondered exactly what real-life events Star Wars could have a similarity to ?

Continue reading

Migrating Oracle Data from Windows to Linux using DataPump

It was a dark, stormy night in Redwood Shores. Only a single light burned at Oracle Towers. The Marketing Department was still locked in conference.
Countless flip-chart sheets littered the room, the result of thought-showers, story-boarding and numerous break-out imagineering sessions.
The challenge with which they had grappled all this time ? How to re-brand the long-time staple, but not particularly exciting export/import utility.
Suddenly, one nameless alpha-male ( and it must surely have been a man) rose to his feet, propelled by a lightning strike of inspiration. In a great, booming voice, dripping with testosterone, pelvis-thrusting beneath his ample girth for added emphasis, he announced to the room, “I know, let’s call it Data Pump !”

The name may have changed, the odd bell-and-whistle added, but the purpose remains unchanged. Export/Import ( Data Pump, if you must), is a utility for transferring objects and data from one Oracle instance to another, irrespective of the Operating System on which either the source or target database is running. Continue reading