Enkitec’s Oracle AWR Data Mining Tool

eAdamEAdam is a free tool that extracts from an Oracle database a subset of data and metadata with the objective to perform some data mining using a separate staging Oracle database. The data extracted is relevant to Performance Evaluations (PE) and/or to Sizing and Provisioning (SP) projects. Most of the data eAdam extracts is licensed by Oracle under the Diagnostics Pack, and some under the Tuning Pack. Therefore, in (more...)


Enkitec’s Oracle Database 360-degree View


EDB360 is a free tool that executes on an Oracle database and produces a compressed file which includes a large set of small Reports. This set of Reports provides a 360-degree view of an Oracle Database. EDB360 is mostly used for one of the following 3 reasons, listed here in order of frequency of use: 1) Keystone of an Oracle database Health-Check. 2) Kick-off for an Oracle database Performance Evaluation. 3) High-level (more...)

Parallel Plans

I’ve popped this note to the top of the stack because I’ve added an index to Randolf Geist’s series on parallel execution skew, and included a reference his recent update to the XPLAN_ASH utility.

This is the directory for a short series I wrote discussing how to interpret parallel execution plans in newer versions of Oracle.

How to reconfigure Oracle Restart

The other day I had to reconfigure an Oracle Restart 12c environment, and I couldn't find my blog post on that. It turns out that I never published it here, as my MOS document on this topic was created back in 2010 when this blog didn't exist!

The original document was written for Oracle Restart 11gR2, but it is still valid for 12c. Here it is.


This document is about reconfiguring the Oracle Restart. (more...)


So is out with a number of interesting new features, of which the most noisily touted is the “in-memory columnar storage” feature. As ever the key to making best use of a feature is to have an intuitive grasp of what it gives you, and it’s often the case that a good analogy helps you reach that level of understanding; so here’s the first thought I had about the feature during one (more...)

Beauty of command line – OEM 12c

Why all software should have a command line and automation plugin ? Answer is simple - if you have to repeat number of operation for different targets - scripts can help you save your precious time.

I really enjoy a fact that Oracle added a command line to Oracle Enterprise Manager line, and now you can script lot of boring tasks like adding new administrator to list of users who can access Named Credentials.

To add (more...)

Oracle ASM Filter Driver: First Impressions

This is a very quick post, because I’m about to log off and take an extended summer holiday (or vacation as my crazy American friends call it… but then they call football  “soccer” too). Before I go, I wanted to document my initial findings with the new ASM Filter Driver feature introduced in this week’s 12.1.o.2 patchset.

Currently a Linux-only feature, the ASM Filter Driver (or AFD) is a replacement for ASMLib (more...)

Finding the blocking SQL in a lock wait

One of my pet peeves on Oracle is the inability to find out what SQL took out a lock that another user is waiting. It’s easy to find the waiting user and their SQL with v$session by looking at v$session.event where the event is an “enqueue” (v8 and v9) or “enq: TX – row lock contention” and then looking up their SQL via the v$session.sql_hash_value which joins to v$sql.hash_value for the v$sql. (more...)

Using Oracle Flex ASM with single instance database

Oracle Flex ASM was introduced in 12c version. This is one of the best features introduced with new version in my opinion.

I won’t speak in detail about Flex ASM because you can find more information in documentation. In this post I will concentrate on how Flex ASM handles crash of ASM instance.

For this test I’ve created 2 node cluster - 12c Grid Infrastructure with Flex ASM enabled.

$ asmcmd showclustermode
ASM cluster : (more...)

Oracle’s In-Memory Database: The True Cost Of Licensing

At last, the long-awaited release of Oracle Database has arrived, including the highly anticipated In-Memory Database Option. I have had the pleasure of being involved in the early beta stages of this feature and I have say: I'm impressed. This is truly game changing technology, and has been extremely well designed and implemented by […]

Oracle Multitenant – PDB Clone New Features ( – Part III

As you know, Oracle Database 12c Release 1 ( already released at July 22, 2014. You can download new release from here.

With this new release coming some new features. This article series covers PDB Clone new features of Oracle Multitenant option of Oracle Database 12c Release 1 (

This article is in four parts:
  1. Excluding Data When Cloning a PDB
  2. Non-CDB Adopt to CDB as a (more...) Released With Cool Indexing Features (Short Memory)

Oracle Database has finally been released and it has a number of really exciting goodies from an indexing perspective which include: Database In-Memory Option, which enables specific portions of the database to be in dual format, in both the existing row based format and additionally into an efficient memory only columnar based format. This in […]

How to find out session info about session that comes from remote database through dblink

It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

First of all we need to start script on local database:

SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask :
Remote_db mask:

 INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
-------- ----  (more...)

Standalone sqlplus script for plans comparing

I have a couple scripts for plans comparing:

1. https://github.com/xtender/xt_scripts/blob/master/diff_plans.sql
2. http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active.sql

But they have dependencies on other scripts, so I decided to create a standalone script for more convenient use without the need to download other scripts and to set up the sql*plus environment.
I’ve tested it already with firefox, so you can try it now: http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active_standalone.sql

Some screenshots:


1. plans_active:

SQL> @plans_active  (more...)

EMCTL Blackout Scripts

The uptime metrics for my group are based on the times OEM has recorded for each target.  Timely blackouts, of course, are essential to keeping the numbers up and (more importantly) preventing Target Down notifications from being sent out.

Initially I’d develpoed EM CLI scripts to create and remove the blackouts.  That solution was crisp, well-characterized, and scalable with one huge problem:  for convenience the blackout script should execute from host containing the target and not (more...)

AWR Warehouse, Status

So the AWR Warehouse patches are out, but documentation has not officially caught up to it yet, so as we appreciate your patience.  I thought I would post about what I went over in my webinar last week when I had the wonderful opportunity to speak to the ODTUG members on this feature that everyone is so anxious to get their hands on.

Let’s start with some of the top questions:

1. Can I (more...)

Bug with xmltable, xmlnamespaces and xquery_string specified using bind variable

Today I was asked about strange problem: xmltable does not return data, if xquery specified by bind variable and xml data has xmlnamespaces:

SQL> var x_path varchar2(100);
SQL> var x_xml  varchar2(4000);
SQL> col x format a100;
SQL> begin
  2      :x_path:='/table/tr/td';
  3      :x_xml :=q'[
  4                  <table xmlns="http://www.w3.org/tr/html4/">
  5                    <tr>
  6                      <td>apples</td>
  7                      <td>bananas</td>
  8                    </tr>
  9                  </table>
 10                  ]';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select
  2        i, x
  3   from  (more...)

Excel connect to Oracle – 64bit and 32bit issues

Wow, thanks to

Process Monitor   http://technet.microsoft.com/en-us/sysinternals/bb896645

I was able track down why I couldn’t connect to Oracle from Excel.

I had wanted to try some of the examples Charles Hooper has posted on connecting to and monitoring Oracle, for example


I kept getting the error “Provider not found”
Now what kind of trace info is there for an error like this in Excel? None AFAIK. Time to start guessing.

Oracle Database – Turning on the In-Memory Database option

It is indeed that sample as switching a knob to turn it on. To enable it you will have to set a reasonable among of...

Read More

Oracle Database – Native JSON Support (1)

Oracle Database has now native support build-in for handling JSON (Javascript Object Notation) data. Oracle Database supports JSON natively with relational database features, including...

Read More