R13 – Get Username Displayed Again

One of the things that we lost when we moved from R12 to R13 is the username in the top right corner. For end-users in production it doesn’t really matter as they’ll only be logging in as themselves, however during the implementation in Test we frequently log-in as different personas so it’s useful to see who the user is for your current session.


The initials we have in R13 offer a clue, but in non-Production (more...)

Attribute clustering….super cool

I’ve spoken about attribute clustering before here, here and here. So from that you can probably glean that I’m a fan.

I recently spoke about an example of this as well during my AskTOM Office Hours session which you can watch below:

After posting that video, I had a follow-up question which I thought would be worth exploring. My examples so far on attribute clustering were either loading a table from empty, or issuing (more...)

You need scaling huh? Maybe it’s just ego

I’ve just come back from OracleCode Singapore.  It was a great event – the venue was awesome and the attendees were engaged and interested in the content. But there was one thing that I found amusing (disturbing perhaps?) is the number of times I had people approach me on the topic of scaling.  Conversation would typically run along the lines of:

“What is your recommendation for scaling?”

which almost suggests that (more...)

Checking if the current user is logged into Application Builder

If you want conditional logic in your PL/SQL dependent on whether the current user is also logged into the APEX Application Builder, this can be achieved by reference to:




This will have a value if the user is logged into APEX Application Builder, otherwise it will be null.

Related documentation: https://docs.oracle.com/database/apex-18.1/HTMDB/understanding-substitution-strings.htm#GUID-62FE6E65-265A-4BE4-B04B-F90BDA317328

Database links and SCNs on June 23, 2019

A few days or weeks ago I saw a Twitter post about database links failing on Oracle databases next year. So, I have researched this issue. My company has a bunch of Oracle databases with a web of database links connecting them. After researching this for a while I have convinced myself that our company will not experience an issue. I want to share some of what I have found in this post.

First, I (more...)

From file names to directory hierarchy

I had a fun request come in from a colleague the other day.  They had a simple list of fully qualified file names and they needed to present that data in the familiar hierarchical tree layout. 

To demonstrate, I took a little trip down memory lane Smile and grabbed a subset of presentations I’ve done over the years.

SQL> create table t ( fname varchar2(1000));

Table created.

SQL> insert into t values ('C:\Users\Connor\Presentations\2002\scene_200205. (more...)

Oracle Code : Warsaw – The Journey Home

I woke up at silly o’clock to begin my journey home. I checked out of the hotel and got a taxi to the airport, where I breezed through check-in and security and found myself at the boarding gate 2 hours before the flight. Another hour in bed would have been nice… 🙂

As usual, out came the laptop and I played catch-up on the blog and some of the other stuff I had missed during the (more...)

Developing for the Oracle Database

1 Coding Standards
What this document does not claim to offer is a prescriptive guide on the minutiae of coding standards.

Coding standards and naming conventions, particularly in SQL and PL/SQL, are an emotive and divisive subject.

This is largely the fault of a historical absence of mature IDEs and a lack of any rigorous, generally accepted standards as seen with other languages.

Many developers still hand-craft scripts and routines and the various tools available (more...)

Customized help text in APEX

Yes, I’m finally back. The last post was written right before a complete period of offline before it was even scheduled to be published. That followed with slowly getting back. I have been fully recovered for a while but not gotten back enough to find time to blog. That changes now. So what causes almost a half years time to recover. Going out with the trash. Yes, it really is dangerous and should be avoided (more...)

FBI Limitation

A recent question on the ODC (OTN) database forum prompted me to point out that the optimizer doesn’t consider function-based indexes on remote tables in distributed joins. I then spent 20 minutes trying to find the blog note where I had demonstrated this effect, or an entry in the manuals reporting the limitation – but I couldn’t find anything, so I’ve written a quick demo which I’ve run on to show (more...)

Post Collaborate and on call week post

So, I was at the Collaborate 2018 Oracle user group conference last week and I am on call this week. I have not posted anything about the conference so I thought I would try to get something out there.

I did my Python and Toastmasters talks. I’m not sure how people received these. Both of these talks sort of loaded extra burden on the Oracle DBAs who attended. One talk said they should learn Python (more...)

Branding R13 (pre-Newsfeed UI)

The branding in Release 13 is a touch different from what we had in R12 as we’ve been given a few more options in the Appearance toolkit. Oracle have upgraded Cedar’s partner environment to 18A so we’ve refreshed the look’n’feel. The 18B update later this year brings in the new ‘newsfeed’ responsive UI, but until then let’s see what we can do with the current toolset:


The first choice is for the overall springboard (more...)

Oracle Recruiting Cloud Workshop

This week I was fortunate enough to attend a 3-day workshop on the new Oracle Recruiting Cloud module.

It was my first proper look at the module and I was curious to see what Oracle have done, as there has been a lot of negative publicity about Workday’s new recruitment module (here and here). My first impression is that Oracle have done a really good job. It doesn’t do everything that Taleo Enterprise (more...)

Modifications to hidden parameters between and

I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in This made me check what other parameters (more...)

Oracle MBX 2018 recap

Last week I attended Oracle’s MBX (Modern Business Experience) conference held near the O2 in London. Here’s what I learnt:


It was busy. Really busy. I’ve also been to the MBX in each of the last 3 or 4 years and this was – by far – the biggest event. I think it even took the organisers by surprise how many people were there as the registration was super-lengthy and there weren’t many lunches (more...)

18.2 patch… painless for me

18.2 was released a few days ago, so I thought I’d throw it against my 18c instance and see how things played out.  This was just a single instance database, running with a single pluggable.

Step 1 for me was to download the latest OPatch.  To be honest, I don’t even check whether I already have the latest version in any installed ORACLE_HOME anymore.  If I’m going to apply a patch, (more...)

Where should my new tech path lead?

I’m a BTOODBA (Bitter Twisted Old Oracle DBA)…and a pretty darn good one if I say so myself. I’ve loved working with Oracle since version 7.0 Parallel Server (what a mess that was!) on OpenVMS (loved VMS). I’ve been responsible for small databases, large databases, very large databases. Oracle Apps (now known as e-Biz…I think), Agile (not the methodology, an actual Oracle product), RAC, Golden Gate (near zero downtime server migration!), Exadata…too (more...)

When you screw up … make it positive for your users

Yesterday I was caught up in an interesting SNAFU at my local Supermarket.  All of the checkout registers shut down, thus making it impossible to pay for groceries.  Later on Twitter, the company apologized as we discovered it was actually a nationwide outage!



News of the outage spread like wildfire through the media:





https://www.9news. (more...)

Framework To Run SQL For All Active DB Instances

Requirement is to configure hugepages for multiple RAC database instances.

pmon processes

grid     12692     1  0 09:39 ?        00:00:00 asm_pmon_+ASM1
grid     13296     1  0 09:39 ?        00:00:00 mdb_pmon_-MGMTDB
oracle   13849     1  0 09:40 ?        00:00:00 ora_pmon_DEV1
oracle   13851     1  0 09:40 ?        00:00:00 ora_pmon_QA1
oracle   13854     1  0 09:40 ?        00:00:00 ora_pmon_PERF1
oracle   13855     1  0 09:40 ?        00:00:00 ora_pmon_TEST1
oracle   14998     1  0 09:40 ?        00:00:00 ora_pmon_INT1

Create parameter.sh which will run parameter.sql.
You might be thinking, WTH is (more...)

Getting geared up for Collaborate 2018 #C18LV

I’m leaving for Las Vegas a week from this Sunday for the Collaborate 2018 conference #C18LV.

I’m doing the same two talks that I did in Denver at RMOUG Training Days.

Here are the times and locations for the talks if you will be there at C0llaborate:

Session: 1415: Python for the Oracle DBA
Date/time: Tue, Apr 24, 2018 (09:45 AM – 10:45 AM)
Location: Jasmine H

Session: 1416: Toastmasters for the Oracle DBA
Date/time: Wed, (more...)