Partitioning Enhancements in Oracle Database 12c Release 1

I was planning to cover this subject in a single article, but it got a bit bulky, so I split it down into 6 little articles.

Which shared memory segments belong to my database instance?

Real-life scenario describing troubleshooting of instance startup problems and actions needed to solve them. 

Oaktable World Las Vegas April 15, 2015 at Collaborate



Wednesday April 15 at Collaborate 2015 Las Vegas room Mandalay K
For more information see Pythian’s Blog post.

Screen Shot 2015-04-08 at 10.03.53 AM


Screen Shot 2015-04-08 at 10.06.48 AM



What is Oaktable World ? Oaktable World is a day organized by members of the Oaktable network. The Oaktable network is a network of Oracle database tuning geeks. Among the members are Tanel Poder, Jonathan Lewis, Cary Millsap etc.  Oaktable network was created by Mogens Nørgaard back in 2001 or so and Mogens started (more...)

Hey dude, where’s my memory? part 2

In my previous article I started exploring the memory usage of a process on a recent linux kernel (2.6.39-400.243.1 (UEK2)), recent means “recent for the Enterprise Linux distributions” in this context, linux kernel developers would point out that the kernel itself is at version 3.19 (“stable version” at the time of writing of this blogpost).

The previous article showed that every process has its own address space, and that different (more...)

Tuning Database XQuery Statements (1)

I had a question of a colleague to have a look at a statement that…

ORA-31145 – “Duplicate listener, %s, found in resource configuration”

Today I had an error on my test environment which surprised me…initially… While I was…

Security parameters in 11G and 12C

There are 5 parameters that are all prefixed with ‘sec’ in an 11g and 12c database. Actually that is a lie because one is now deprecated in 12c. They are all, as you might guess related to security. This blog is about changes in the default values and some thoughts about whether or not the default value is appropriate or not.

SEC_MAX_FAILED_LOGIN_ATTEMPTS default 11GR1,11GR2=10, 12c=3

LPAR and Oracle Database

What is LPAR?

LPAR stands for Logical Partitioning and it's a feature of IBM's operating system AIX (Also available in Linux). By abstracting all the physical devices in a system, LPAR creates a virtualized computing environment.

In a server; the processor, memory, and storage are divided into multiple sets. Each set in a server consist of resources like processor, memory and storage. Each set is called as LPAR.

One server can have many LPARs operating (more...)

The Rise and Fall of the NoSQL Empire (2007–2013)

The NoSQL camp put performance, scalability, and reliability front and center but lost the opportunity to take the relational model to the next level because—just like the relational camp—it mistakenly believed that normalization dictates physical storage choices, that non-relational APIs are forbidden by the relational model, and that “relational” is synonymous with ACID (Atomicity, Consistency, Isolation, and Durability). The NoSQL camp created a number of innovations: functional segmentation, sharding, replication, eventual consistency, and schemaless design. (more...)

Which file system to source for adop

One of the big changes for Oracle Applications DBAs in EBS 12.2 is the introduction of online patching.  Instead of using adpatch to apply application patches we now use adop.  In order to allow for online patching Oracle has introduced the dual file system (Run and Patch).  I'm not going to go into a complete explanation of the dual file system in this post, but you can think of it as being two copies (more...)


Everyone gets caught out some of the time with NOT IN.

NOT IN is not the opposite of IN.

This came up in a (fairly typical) question on OTN recently where someone had the task of “deleting 6M rows from a table of 18M”. A common, and perfectly reasonable, suggestion for dealing with a delete on this scale is to consider creating a replacement table holding the data you do want rather than deleting the (more...)

(OT) an idea for Easter (and well…every day)

I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room.  To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case of reading a page or two here and there, rather than a true extended session of relaxed reading.

The (more...)


I watched Interstellar last night at a mates house.

Where to start with this film?

  • Visually it is fantastic. Lots of epic scenes. Definitely great eye candy.
  • The story is pretty basic. I won’t give anything away but there’s nothing new here.
  • According to all the stuff on the net, it is scientifically on point.

What’s my gut feel?

  • Way too long. Lots of “setting the scene” and comparatively much less of the stuff you really want (more...)

Hey dude, where’s my memory?

This blogpost is about finding the actual amount of memory a process is taking. In order to do so, this post dives into the memory mechanisms of Linux. The examples in this article are taken from an Oracle Linux version 6.6 server, with kernel 2.6.39-400.243.1 (UEK2). This is written with the Oracle database processes in mind, but actually uses examples of a processes running ‘cat’, which means the contents of (more...)

Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my "11g features for Developers" presentation at the Australian Oracle User Group conference.


I think the picture is from the movie "Indiana Jones and the Last Crusade"… where they sought the Holy Grail.

I used the picture because I said that Edition Based Redefinition (EBR) was the Holy Grail of 24/7 Oracle based applications.  (Like most Oracle presentations at the (more...)

OakTable World at IOUG COLLABORATE15

Guess what? OakTable World at IOUG C15 is happening. Last year, we’ve had awesome sessions and wonderful attendees. It was so successful that we have now a bigger room this year (there were other reasons too but hey — we can fit more people now!).

What: OakTable World C15
When: Wednesday, 15-Apr-2015, 8:00am – 5:30pm
Where: Mandalay Ballroom K

I really hope if you are reading this, you would be in Vegas between 12th (more...)

Find Users with DBA Roles

This sounds like a simple request doesn’t it?

Simple if there are no roles which are granted DBA role and allow me to show what can go wrong.

Create ROLE “secret”, Grant DBA to “secret”, Grant “secret” to USER “michael”

ARROW:(SYS@hawklas):PRIMARY> create role secret;

Role created.

ARROW:(SYS@hawklas):PRIMARY> grant dba to secret;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY> grant secret to michael identified by michael;

Grant succeeded.


When a simple SQL is used, “secret” is a ROLE NOT USER.


One Cause of ORA-01092

I saw the following error in a job which had just recreated the control file of a test database after cloning:

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   3
2   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   (more...)

SQLcl, a revolution for SQL*Plus users

What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable. The tool does need any Oracle … Continue reading SQLcl, a revolution for SQL*Plus users

The post SQLcl, a revolution for SQL*Plus users appeared first (more...)

Observer effect

In physics, one important limitation of any experiment is the fact that an act of observation inevitably interferes with the observed process (“observer effect”). Same thing is true about databases. It is a well known fact that, for example, turning on tracing can significantly slow down the process for which it’s enabled. But there exist even nastier forms of this effect: for example, when you try to trace a SQL statement using nested loop batching mechanism, (more...)