This is the text of the “whitepaper” I submitted to DOAG for my presentation on “Core Strategies for Troubleshooting”.


In an ideal world, everyone who had to handle performance problems would have access to ASH and the AWR through a graphic interface – but even with these tools you still have to pick the right approach, recognise the correct targets, and acquire information at the boundary that tells you why you have a performance (more...)

My agenda

It’s gettting to that time of year, so here are some of the sessions I’ve pencilled in for UKOUG Tech 15:

Monday 7th:

9:00 – 9:50: Tom Dale – Fivium : An insight into Oracle Standard Edition, what’s included, what’s not, and how to plug some of those holes!

11:20 – 12:10: Me (et. al.) : Cost Based Optimizer – Panel Session

12:20 – 13:10: Franck Pachot – DBi Services : All About Table (more...)

Where did my triggers go ?

You need to be careful when coding and using triggers when it comes to Datapump (or anything that transposes triggers between schemas).  A lot of people make assumptions about what will happen with their triggers, and often get a nasty shock when they see the reality.  Lets explore with an example

I’m going to add three triggers to the standard EMP table in the SCOTT schema

SQL> connect scott/tiger
SQL> create or  (more...)

ORA-24247: network access denied by access control list (ACL)

SQL> begin
2 3 4
5 /
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 19
ORA-06512: at “SYS.UTL_TCP”, line 280
ORA-06512: at “SYS.UTL_SMTP”, line 163
ORA-06512: at “SYS.UTL_SMTP”, line 199
ORA-06512: at “SCOTT.SEND_MAIL”, line 8
ORA-06512: at line 2

This error message means that no access control list has been assigned to the host you (more...)

Tech 15

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 (more...)

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 (more...)

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 (more...)

Oracle Forms is heading to the clouds and to UK!

As I have been preaching from my soapbox for years now, Oracle Forms will remain a constant, supported development tool to build on premise, enterprise level, data entry desktop applications. However this time, Oracle has joined me by declaring their commitment to Oracle Forms, as Oracle Forms 12c was released on the heels of OOW. Oracle Forms 12c can be downloaded here. Our customers and team have been anticipating Oracle Forms 12c, and it is great to finally have it available.

It’s (more...)

Countdown to Perth !

I’m speaking at the Perth leg on the OTN APAC tour on December 2nd, eCentral TAFE, East Perth

This is a great event with local and international speakers all giving their time and knowledge for free to help you with your Oracle technology.

If you’re in Perth, then come along for some great education.  Even if you cannot attend the conference, if you are in the area, come along and say Hi.  Part (more...)

Apex upgrade 4.2 to 5.0.2

Just a quick note for anyone upgrading Apex on their systems.

The installation (into a non-multitenant instance) went through with no problems, but tracing the installation suggests it will flush the shared pool 6 times during installation/upgrade.

That might have some impact on other applications/sessions running on that database, so best to find a quiet time to do it.

A justification for referential integrity constraints

Yes, I know what you’re thinking. 

“Here we go again.  Another database dude about to get stuck into us telling us about the importance of foreign keys for data integrity”

and yes, I could easily do that.  But you’ve seen that before (and by the way, it’s of course still totally valid – you should have all those integrity constraints defined!)

But here’s another reason, albeit a little tongue in cheek Smile


Countdown to Sydney

I’m speaking at the Sydney leg on the OTN APAC tour on November 30th, 394 Lane Cove Rd, North Ryde

This is a great event with local and international speakers all giving their time and knowledge for free to help you with your Oracle technology.

If you’re in Sydney, then come along for some great education.  Even if you cannot attend the conference, if you are in the area, come along and say Hi. (more...)

Docker Data Containers


photo by

One challenge with Docker is having persistent storage for a container especially when that container gets restarted on another VM host and we want it to point to the same data. If we add in Delphix with Docker we can easily move persistent data and a docker container to a new host.

For the docker container we will use wordpress that leverages a MySQL database for it’s persistent datastore.
In this example I (more...)

OPatch bugs when applying Grid Infrastructure

For one of my clients, I experienced several issues with applying PSU with opatch

There were some code changes in opatchauto, which are not yet production-ready. I am looking forward to seeing a new opatch release (maybe or 11) which has these issues fixed.

    “opatchauto apply -analyze” is (more...)

CLOBS, from 11g to 12c

If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c.  Notice the subtle difference:

In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default

SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PERMITTED

In12c, SECUREFILE lobs will be the default.

SQL> select name, value
  2  from v$parameter

Actively Manage Your OMR with EMDIAG

Oracle provides the OMR analysis tools EMDIAG and repvfy so we can perform detailed management repository analysis and maintenance just like the pros.

Werner de Gruyter (Yoda), the father and caretaker for EMDIAG, has provided a custom Information Publisher report that you can use to manage your system on a regular basis.

Create EMDIAG Report

EM CLI allows you to create an IP report by importing an XML definition of the report.  Create and save the XML (more...)

Action Links in OBIEE 12c – Part 1


With the release of OBIEE 12c, let’s take a look at Action Links and how things may be different compared to the previous release, 11g. Over this three part blog series, we’re going to cover the more popular link types, which are navigating to BI content, and navigating to a web page. However, to sweeten the deal, I’ll also include some tricks for your tool belt which well enable you to do the following:


You dont need that sequence number

I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code:

SELECT my_sequence.nextval INTO :some_variable FROM dual;
INSERT INTO my_table VALUES (:some_variable, ....);

My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before you inserted it ? It is simply, easier, and more efficient just to get it back from the insert (more...)

Update hinted for wrong index

I worked with our support team to improve the performance of a PeopleSoft Financials update statement yesterday. The update statement had an index hint already in it but the index was not the best one of the available indexes.

Here is the original update statement:

 BANK_CD = :2,
 WHERE  (more...)

OBIEE 11g and Essbase – Faking Federation Using the GoURL

This blog is going to address what happens when we can’t take advantage of the Admin tool’s powerful vertical federation capabilities when integrating relational stars and Essbase cubes. In the Admin tool, synonymously referred to as the RPD, vertical federation is the process of integrating an aggregate data source, in this case Essbase, with a detail level source from a data mart. This technique not only has the ability to increase query efficiency and decrease (more...)