New Version Of XPLAN_ASH Utility – In-Memory Support

A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.

As usual the latest version can be downloaded here.

This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and

As an extra however, (more...)

Datafile without backups – how to restore?

Have you ever had a problem with restoring datafiles without any backups available? It's easy, of course if you have all archived logs from the time datafile was created. Please check it here: Re-Creating Data Files When Backups Are Unavailable. Moreover, RMAN is clever enough to create empty datafile automatically during restore phase and then recover it using archived logs. So far, so good, but...

Map OS Groups To Administrative Privileges After Installation

RSS content

During installing database software, user is prompted to enter names of various operating system groups mapping to various administrative privileges (SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSDG). One might map one operating system group to multiple administrative privileges if role separation is not desired.  In case the need for role separation arises later, the mapping can be specified by updating  $ORACLE_HOME/rdbms/lib/config.c file and then relinking it. This post explains the various steps.

While installing (more...)

Riga Dev Day 2015 : The Journey Begins

My first flight was 06:20, so I had to leave the house at 04:00, which meant getting up at about 03:00. Yuck!

The first flight was about 90 minutes from Birmingham to Frankfurt, with the smoothest landing I can ever remember in a plane the size of an A320. I was meant to have a 65 minute changeover before the flight from Frankfurt to Riga. A one hour changeover at Frankfurt is too short, but the (more...)

Share your interesting SQL or PL/SQL experience on stage, on camera, or in print

If you have had interesting experiences with SQL or PL/SQL in your career, here’s your opportunity to share them on stage, on camera, or in print. Send your stories to and we’ll forward them to the SQL evangelists team headed up by Steven Feuerstein. You may be selected to tell your stories on stage at the YesSQL! presentation at the winter conference next Tuesday or your stories may be recorded on camera or (more...)

Method R and parallelism (another real-life example)

In my previous post I mentioned method R as probably the most efficient approach to SQL optimization. Occasionally, however, one may encounter a situation when following this method literally can lead to trouble.
Consider this example (once again, the query is still running, so the only reliable diagnostic tool at our disposal is SQL real-time monitor):

SQL Plan Monitoring Details (Plan Hash Value=776230426)
| Id   |              Operation              |           Name            |  Rows   | Cost  |    (more...)

How to resolve the text behind v$views?

This is a common problem I have and I never write it down (except now). For example, today I wanted to know what the valid parameters for _serial_direct_read were:

SQL> select * from v$parameter_valid_values where name ='_serial_direct_read';

no rows selected

OK so if Oracle doesn’t tell me then maybe I can work it out? Getting the view_text has worked in the past:

SQL> select view_name, text_vc from dba_views where view_name like '%PARAMETER_VALID_VALUES'

VIEW_NAME                          TEXT_VC
----------------------------------  (more...)

LOB Space

Following on from a recent “check the space” posting, here’s another case of the code not reporting what you thought it would, prompted by a question on the OTN database forum about a huge space discrepancy in LOBs.

There’s a fairly well-known package called dbms_space that can give you a fairly good idea of the space used by a segment stored in a tablespace that’s using automatic segment space management. But what can you think when a (more...)

Converting non-CDB database as PDB in existing CDB

This is a short article on converting existing non-container (non-cdb) 12c database as pluggable database (pdb) to existing container database (cdb).

Version of Non-CDB database =

Version of CDB database =

Following steps will plug-in non-cdb database into cdb as pdb and also will upgrade the version to

My non-cdb database name is deo12c

My cdb database name is deocdb

Step 1) Make (more...)

A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911

SQL> @test1
SQL> set echo on
SQL> select 'Comment->' from dual; /*Andrew was (more...)

Cluster Cache Coherency in EM12c

January is winding down and RMOUG Training Days 2015 is just around the corner, taking up much of my after work hours.

With that, we are going to discuss a great performance console in the EM12c cloud control-  Cluster Cache Coherency.

Cluster Cache Coherency

Optimization for an Oracle Real Application Cluster, (RAC) can be a daunting tasks for those that aren’t familiar with some of the most common issues to look for.  Although many (more...)

How to Set Axis Max Value of AnyChart in APEX Dynamically

I just created a one-page APEX application to show performance chart of our main production server based on Active Session History data. We publish it on a big monitor so we can see the performance chart during the day. It’s not a big deal but it’s really useful. I used 2D stacked column chart and make the chart simple (contains only 3 colors: green for CPU, blue for user IO and orange for all other (more...)

Oracle XFILES now on GitHub

The demonstration environment for Oracle XML DB called XFILES is now on GitHub. As stated…

Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch


Oracle Database 12c brings us many new features including: the long needed ability to run OPatch and query the patch software installed in the Oracle Home programatically through SQL using the new DBMS_QOPATCH package.

If you’re a DBA working in an environment where patching consistently among databases is important, this is a welcomed enhancement. Sometimes one-off critical bug fix patches are important in the environment. Other times regular and consistent application of the quarterly (more...)

I Have Lots Of Oracle Database Server Power But Performance Is Slow/Bad

I Have Lots Of Oracle Database Server Power But Performance Is Slow/Bad

Oracle Database parallelism and serialization is what we as Oracle Database Administrators live and die for. You have a screaming fast Oracle Database system and there is lots of computing power available.

But performance is unacceptable; users are screaming, the phone is ringing, and those fancy dashboards are flashing like it's Christmastime.

What is going on?! What can I do about it?! That's (more...)

Targets in Perpetual Pending State

After my issues with the patch bundle some of my database targets ended up in an odd ‘Status Pending’ state.  I received this guidance from RachelB on the OTN site for OEM Community at

“The database system target is one of those targets that has it’s status evaluated in the repository.  Normally if the status is not changing, it’s a good idea to check the internal repostory (dba scheduler job) which (more...)

Arizona User Group Meeting tomorrow

Here is the link: url

It looks like our meeting tomorrow will be on Oracle 12c new features.  I’m looking forward to participating. :)

– Bobby

Bitmap Counts

In an earlier post (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant bitmap conversion to rowid when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to force (or the now-deprecated similar). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few (more...)

enq: TM – contention due to parallel DML and foreign keys

This is a write-up of an issue I recently posted to the OTN discussion forum ( I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.

The test was performed using Oracle Enterprise Edition on Linux.

Problem statement

A parallel delete blocks insert into dependent (more...)

Log Buffer #406, A Carnival of the Vanities for DBAs

This Log Buffer Edition covers blog posts from various bloggers of Oracle, SQL Server and MySQL.


Sync tables: generate MERGE using Unique constraint.
What Hardware and Software Do YOU Want Oracle to Build?
There were a number of new features introduced in Ops Center 12.2.2. One of the shiny ones is an expansion of the backup and recovery capabilities to include Proxy Controllers.
Want to Be a Better Leader? Answer One Question.