The Little Oracle Users Group That Could

It requires a vast amount of work to organize a tech­nical conference and publish a printed journal every quarter. No sooner has a conference ended and a journal mailed than it is time to start work on the next conference and the next journal. NoCOUG has no funding from Oracle Corporation and a miniscule budget compared to the national and inter­national user groups, but the NoCOUG volunteers have always managed to pull it off, quarter (more...)

NoCOUG Journal: A Few of My Favorite Things

As the editor of the NoCOUG Journal for ten years and counting, I’ve had the interview to interview some of the best minds in the Oracle world. These are a few of my favorite things they said.(read more)

How to rebuild a 12 TB index that you accidentally dropped from a 55 TB table

Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)

SQL pretzels created while experimenting with hallucinogens?

In an interview for the NoCOUG Journal (, Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?” Steven replied: “Its place is proven: SQL is not a (more...)

#doag2016 my picks and suggestions

As many conferences evolve over the years, the number of sessions on offer can easily be overwhelming. I have overheard many conference attendees wrestling with their choices for what to see and which sessions to attend.

For DOAG 2016 I have a short overview with my picks and with one or two tip-sessions. I hope this helps, though it is just my personal preference of course…

Please note that this post is based on the (more...)

HOST Options for local_listener

For a long time, I was using *.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1551))’ without specifying the host name since it makes the configuration more dynamic.

Then one day, I ran into issues which I don’t remember and started to use host name for local_listener.

Today, I ran into issues after cloning VM and host name changed causing database not to start and I am now back to using local_listener without host name.

$ sysdba

SQL*Plus: Release 12.1. (more...)

What is overloading and how and when do I use it

Dear Patrick,

Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?

Ramesh Cumar

Dear Ramesh,

Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)

IOT limitation

In the right circumstances Index Organized Tables (IOTs) give us tremendous benefits – provided you use them in the ideal fashion. Like so many features in Oracle, though, you often have to compromise between the benefit you really need and the cost of the side effect that a feature produces.

The fundamental design targets for an IOT are that you have short rows and only want to access them through index range scans of primary (more...)

Oracle Public Cloud and Kafka – Events powering the cloud – The Oracle PaaS Cloud Event BusHub

Apache kafka.pngEvents are quite relevant in modern computer architecture. At various levels of the stack. Events can represent various things – from IoT based measurements and logistical updates to web site activities and business transactions to operational IT metrics and situations. Events can be produced at peak speeds and high volumes – and may require rapid and robust processing. Apache Kafka has grown into almost the de facto foundation for event processing. Apache Kafka provides high (more...)

Application Container Cloud and Multi Tenant Services – Oracle embracing 3rd party technologies and open source projects

The Oracle PaaS cloud is turning into the next generation application server platform. More specifically: Oracle has announced the roadmap for the Application Container Cloud Service (not to be confused with the plain Container Cloud Service which runs pre built Docker containers without looking into them). The Application Container Cloud Servic

  • accepts and runs applications (not containers) implemented in a number of technologies – Node.js, Java SE, PHP, Python, Ruby, Go and Spark/Hadoop (more...)

Oracle Database 12c STIG Compliance Standard sample code is here!

One of the most requested features in the compliance management area has been a compliance standard for Oracle Database 12c. As most of you would know, Oracle Database 12c has been out for quite some time, and indeed Larry Ellison has just announced at OOW2016 availability of Oracle Database 12cR2 in Oracle’s public cloud offering. However, as I discussed in another post earlier this year, creating a new STIG compliance standard is not a simple (more...)

Adventures in the Cloud

I just got back from a whirlwind tour that included a trip to San Francisco to visit the Snowflake HQ, attend the annual Oracle ACE Directors briefing, meetings and events at Oracle OpenWorld (OOW), speaking at OakTable World (OTW), then off to Chicago to speak at Enterprise Dataversity. Yes it was packed. And lots of […]

PK Histogram

One of the little myths of Oracle appeared on the Oracle-L list server a few days ago – the one that says: “you don’t need a histogram on a single column unique/primary key”.

Not only can a histogram be helpful on a column that’s declared to hold unique values, the optimizer may even spot the need automatically. It’s a little unusual (and probably the result of poor programming practice) but it does happen. Here’s an (more...)

Comparing workloads across databases

Does this question sound familiar? “We are running the exact same workload in UAT as we do in production, but it behaves completely different. Can you help?”

If it does, then you might find the following post useful, provided you are appropriately licensed to use this feature anyway. I have been working on AWR data for a long time, and one of the best ways to do so according to David Kurtz and others, (more...)

Oracle Enterprise Manager Cloud Control: Write Powerful Scripts with EMCLI

Last week, I attended the Oracle Open World and did a presentation about writing scripts with EMCLI. If you’re not familiar with EMCLI, it’s the command line interface for Oracle Enterprise Manager Cloud Control. Here’s my presentation:

Although EMCLI is a very specific topic which appeals only to advanced users, a lot of people attended my session. I would like to thank Ray Smith (IOUG Director (more...)

Oracle SQL Pattern Recognition – introducing the Match Recognize operator

imageOracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In this set, each record satisifies a certain condition. The fact that in a set of records these conditions are met – in a certain (more...)

OBIEE12c – Upgrading to Version


The new version of OBIEE 12c, to be exact, is out, so let’s talk about it. It’s my intent that after reading this, you can expect some degree of comfort in regards to possibly doing this thing yourself (should you find yourself in just such a circumstance), but if not, feel free to drop us a line or give us a ring. It should be noted that Oracle documentation explicitly (more...)

When would you use a normal table function?

Dear Patrick,

Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?

Erik van Roon

Dear Erik,

Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)

Flashback database – dont forget the standby

Today an  application team had a problem with a production system and they wanted the database restored to a point a couple of days ago. It is a configuration management system and they figured they could lose any changes . Their call.

I did all the good things

  • Checked we had a change in place
  • Blacked out primary and standby in OEM
  • Checked flashback was on  –
select flashback_on from v$database;

Read Only Users and Database Performance

One of the questions we see being asked reasonably frequently is how to allow read only access to someone who wants to view database performance issues, using tools such as Real Time ADDM, ASH Analytics and so on. This is generally asked for someone like an application developer, who can use this information to identify performance issues in Production and then fix the root cause in their Development environments.

Well, that’s a reasonable question to (more...)