Top 60 Oracle Blogs

Recent comments

November 2015

More semver functionality

So the main functionality I need from the semver package that I am writing, is the "satisfies" functionality. This is the function, that can define if a certain version string pattern is within a set of ranges of different versions.

For example, I want to know if version 1.2.* is satisfied with the following ranges: <=1.2.3 or >1.2.0 or between 1.4.1 and 1.4.6. But before I get there I need some more supporting functions. I my last entry, I had implemented some of the basics, and now I have completed a few more

PL/SQL Formatting : More pearls of wisdom from Bryn

glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

Tech 15

Updated 27th Nov 2015:

Thanks for all the questions so far – there are plenty of useful topics coming out. At this rate I may have to pass some of these on to the round-table that Tony Hasler.

I’ve made a couple of comments in response, but generally I plan to avoid making any comments until after the panel.

Monday 7th Dec: 11:20 – 12:05

I’ve arranged a panel session on the Cost Based Optimizer for UKOUG Tech 2015, with Maria Colgan, Nigel Bayliss, and Chris Antognini joining me to answer (and maybe argue about) questions from the audience.

To keep things moving along, we aim to have a few questions available before the day and collect a few questions at the door as well as accepting questions from the floor. Martin Widlake and Neil Chandler will be acting as MCs, wielding the microphones, and collecting questions at the door.

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns. Let’s cause an obvious name clash…

The table DUAL has a column called DUMMY with a single row with the value ‘X’.

KISS video series for Analytic functions

Analytic functions still bamboozle many SQL practitioners out there.  So I’m building a suite of videos to walk people through some of the common questions we get asked that we need to solve with SQL, and look at how we can often solve them most simply using Analytic functions.

It will all be based on the KISS principle – Keep It Simply SQL, as it pertains to understanding the Analytic syntax.

You can find my introductory video here, with more to come as we solve problems simply with (analytic) SQL.  There is also an “Analytics” playlist on my channel, which will grow each time I add a new video.

I hope you enjoy it – and feel free to comment or offer feedback.


SEMVER utility for plsql

So anyone doing software releases knows the pain of doing versions correctly for the different releases. One solution to that is using SEMVER ( which tries to provide a solution for that. Go to the website and read up when you change the different parts of the version (x.y.z = major.minor.patch), and what the rules are around comparing different versions.

ORA-14758: Last partition … cannot be dropped

As a DBA, its awesome when you have the Partitioning option at your disposal.  So many cool things suddenly become either possible or easier.  For example, all of a sudden you can separate the data of different time ranges into different partitions. But hand in hand with that, was that fear that one day we’d be getting nasty stares from either managers or customers, when the following happened:

As a DBA, its awesome when you have the partitioning option at your disposal.  So many cool things suddenly become either possible or easier.  But hand in hand with that, was that fear that one day we’d be getting nasty stares from either managers or customers, when the following happened:

Why ALL Tools in at Your Disposal ARE IMPORTANT

After my AWR Warehouse session at DOAG in Nuremberg, Germany last week, an attendee asked me if he really needed to use trace files anymore with the value provided by AWR, (Automatic Workload Repository) and ASH, (Active Session History.)  I responded that trace files were incredibly val

Promoting Unmanaged Targets Automatically

Recently a customer asked if it was possible to promote unmanaged targets automatically without any interaction. They were already using auto-discovery.

The answer is yes, and of course as soon as you see words like “without any interaction” you know there’s going to be scripting involved. In the Enterprise Manager world, scripting is handled by the EM Command Line Interface, better known as EMCLI. I’ve posted some example scripts of using EMCLI a while back (see this post), but I thought it was worthwhile to show you how this particular request is handled as well. If you have the latest bundle patch on either or, you can also use additional parameters to the get_targets verb to list discovered targets (-unmanaged). This includes listing the associated instance targets if you want to promote a RAC database (-associations).