Unique all the things … including your pluggables

Uncategorized
| Dec 18, 2019

A quick tip just in time for Christmas Smile

I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database!

That’s because I was! Smile Here is how it happened:

  • I have a 19c database, called it DB19, and it had a (more...)

Listener log data mining with SQL

Uncategorized
| Dec 15, 2019

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.


16-DEC-2019 09:54:20 * service_update * db18 * 0
2019-12-16T09:54:23. (more...)

dbca silent mode – Windows

Uncategorized
| Dec 12, 2019

Just a quick tip that often catches me out. If you are like me, you have long since tired of clicking Next, Next, Next, … through the GUI when you want to quickly create a database. Many people work around this by storing a set of database creation scripts. However, you can do even better. The Database Creation Assistant (dbca) can also be used at the command line and in silent mode.

On Windows, this (more...)

2019-what grabbed your attention

Uncategorized
| Dec 9, 2019

Here are the blog posts that you hit on most this year, with the most viewed entry on top. Unsurprisingly is it related to my bugbear with the OpenWorld catalog. I mean, every conference organizer must know that the one thing the attendees will always want is to get access to all of the content. Questions on UTL_FILE often come up on AskTOM, so it is unsurprising to see UTL_FILE pop up on the list. (more...)

Exadata storage indexes

Uncategorized
| Nov 19, 2019

We had a question on AskTOM inquiring about how to handle the issue of only 8 storage indexes being possible on an Exadata engineered system. If you are unfamiliar with what a storage index is, they are part of the suite of features often referred to as the “secret sauce” that can improve query performance on Exadata systems by holding more metadata about the data that is stored on disk. You can get an introduction (more...)

Do you work in I.T?

Uncategorized
| Nov 13, 2019

If you do, then you know that only 20% of your I.T work is spent at the office. And no, I’m not talking about telecommuting or working remotely here. I’m talking about the 80% of our time we spend handling all those I.T calls from friends and family Smile

So I was motivated to put together this little ode – dedicated to all of us that have to go the extra mile to keep (more...)

Cloning a schema with one line

Uncategorized
| Nov 6, 2019

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional (more...)

DML error logging redux

Uncategorized
| Oct 30, 2019

I posted a nice little feature of DML error logging recently, and a comment came in mentioned that caution is needed with DML error logging because the errors are logged persistently. Hence if you ran a load multiple times, or multiple sessions were utilizing the facility, then the error logging table can quickly become a soup of data that cannot be tracked back to the code your own session was running.

By default this is (more...)

The holistic SQL tuning series

Uncategorized
| Oct 24, 2019

I did a set of articles for Oracle Magazine on a more holistic view of SQL tuning. What do I mean by “holistic”? It was a reflection of a common problem that I see when questions come into AskTOM, or when people in the community approach me at conferences, namely, there is an inclination to dive straight into the deepest levels of the tuning exercise:

  • “What index should I create?”
  • “Should I increase the (more...)

The definition of proof

Uncategorized
| Oct 2, 2019

One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size.

Personally I think that is baloney (Ed-in reality, as an Australian I have a slightly more powerful choice of term, but lets keep things PG-rated 🙂). But when I recently challenged (more...)