Auditing on Oracle Database in a Nutshell (11gR2)

In an Oracle Database we can mention following auditing types: Mandatory Auditing Standard Auditing Fine-Grained Auditing SYS Auditing Mandatory Auditing causes database start-up/shut-down and SYSDBA-SYSOPER login logout information to be written into AUDIT_FILE_DEST. This auditing cannot be turned off and it's always written into operating system directory specified with


One use for the addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
   v_char varchar2(2000);
   select high_value into v_char
   from user_tab_partitions a
   where a.table_name = i_tab
   and a.partition_name = i_part;
   if v_char like 
     'TO_DATE(''%'', (more...)

Quiz Night

How many rows will you return from a single table query if you include the predicate

        rownum > 2

in the where clause.

Warning: this IS a catch question

To make it easier and avoid ambiguity, you may assume the table is the standard SCOTT.EMP table.


Part 2:

This posting was prompted by noticing a note that Dominic Brooks posted a few months ago.

Can you supply a workaround for the little oddity (more...) fails with Unable to locate any IB switches

With the release of Exadata X5 Oracle introduced elastic configurations and changed the process on how the initial configuration is performed. Back before you had to run which would go across the nodes and change all the settings according to your config. This script has now evolved and it’s called which is part of OEDA (onecommand). During one of the recent deployments I ran into the below problem:

[root@node8 linux-x64]# ./applyElasticConfig. (more...)

Notes on analytic technology, May 13, 2015

1. There are multiple ways in which analytics is inherently modular. For example:

  • Business intelligence tools can reasonably be viewed as application development tools. But the “applications” may be developed one report at a time.
  • The point of a predictive modeling exercise may be to develop a single scoring function that is then integrated into a pre-existing operational application.
  • Conversely, a recommendation-driven website may be developed a few pages — and hence also a few (more...)

Instance stats


While reading a posting by Martin Bach on a new buffering option for 12c I was prompted to take a look at another of his posts on the instance activity stats, which reminded me that the class column on v$statname is a bit flag, which we can dissect using the bitand() function to pick out the statistics that belong to multiple classes. I’ve got 2 or 3 little scripts that do this one, (more...)

How to configure Link Aggregation Control Protocol on Exadata

During a recent X5 installation I had to configure Link Aggregation Control Protocol (LACP) on the client network of the compute nodes. Although the ports were running at 10Gbits and default configuration of Active/Passive works perfectly fine the customer wanted even distribution of traffic and workload across their core switches.

Link Aggregation Control Protocol (LACP), also known as 802.3ad is a methods of combining multiple physical network connections into one logical connection to increase (more...)

Publish a REST service from PL/SQL to handle HTTP POST requests – using the embedded PL/SQL gateway

Oracle Database can act as an HTTP server – using the Embedded PL/SQL Gateway (the 10g successor of the MOD_PLSQL gateway). With just a few statements, we can have the Oracle Database become a listener to HTTP requests (GET or POST). When requests are received at the configured host, port and URL, the request is passed to a PL/SQL procedure that handles it and prepares a response.

In this article, we will expose a REST (more...)

Troubleshooting Target Status Availability Issues

If you’ve been using EM12c (or any of its precursors for that matter), you’d know that it can sometimes be problematic to troubleshoot an availability issue for targets.  You can see they might be up (hopefully!), down, pending, unreachable or showing a metric collection error, but understanding what’s causing that particular status (and indeed why it can sometimes be wrong) can be difficult at times.

Thankfully there’s a couple of Information Publisher reports (more...)

Parallel Query

According to the Oracle Database VLDB and Partitioning Guide (10g version and 11g version):

A SELECT statement can be executed in parallel only if the following conditions are satisfied:

  • The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
  • At least one table specified in the query requires one of the following:


Here’s a question to which I don’t know the answer, and for which I don’t think anyone is likely to need the answer; but it might be an entertaining little puzzle for thr curious.

Assume you’re doing a full tablescan against a simple heap table of no more than 255 columns (and not using RAC, Exadata, In-memory, etc. etc. etc.), and the query is something like:

select  {columns 200 to 250}
from    t1
where    (more...)

Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE

This article is small and simple. It discusses how from PL/SQL an HTTP POST request can be made to a REST service. This particular service is exposed at http://localhost:9002/cinema and it expects a POST call.

Making HTTP requests from PL/SQL is fairly simple, using the supplied package UTL_HTTP. Starting in Oracle Database 11g, some security constraints are in force around network interactions. This means that before from a specific database account a PL/SQL unit can (more...)

Oracle Pre-Built Developer VMs and VMBox

Virtual machines (VM) are not new –it has been around for quite some time, and as a consultant I find myself use them all the time. As a matter of fact, just on my laptop and external drive there are at least 15 or 20 different virtual environment which I use for testing, experimenting, and for creating new blog posts.

The thing with virtual machines that you need to be a little more than just (more...)

Parallel Execution

This is another little reference list I should have created some time ago. It covers a series of posts on interpreting parallel execution plans and understanding where the work happens.

Oracle things that piss me off (pt 2) – No Direction

The SQL Developer team has been chugging forward with it's SQL Command Line (sqlcl) tool.

As I developer, I understand where they are coming from. SQL Developer benefited from being able to run scripts built for the SQL*Plus command line tool. Then there's the temptation to add a few more useful titbits to the tool. And if it is built 'properly', then it would be relatively easy to decouple it from the GUI and (more...)

The Great Hypervisor Bake-off: VMware ESX vs Oracle VM


This is a very simple post to show the results of some recent testing that Tom and I ran using Oracle SLOB on Violin to determine the impact of using virtualization. But before we get to that, I am duty bound to write a paragraph of text featuring lots of long sentences peppered with industry buzz words. Forgive me, it’s just the way I’m wired.

It is increasingly common these days to find database environments running (more...)

Oracle ACE Award

I have just learned that I have been nominated and accepted as an Oracle ACE. I would like to thank Jonathan Lewis for the nomination and Oracle Corp. for the recognition.


It’s a tremendous honour for me to receive this award. I just hope I can live up to it by continuing to serve the community, write some more blog posts (here) that people find useful, keep organising the UKOUG RAC, Cloud, Availability and Infrastructure (more...)

Re-Adding a Database to the AWR Warehouse Without Losing Historical Snapshots

There may be a reason that one needs to re-add a database to the AWR Warehouse.  This is a new opportunity for me to learn and offer assistance, but I’m working off the grid to figure out a solution.  Luckily, it’s just a matter of creating a new process from pre-existing code and processes.

The process requires both the AWR Warehouse UI and a SQLPlus connection.

1.  Disable the snapshots

2.  Extract the AWR for (more...)

Fourth International NoCOUG SQL Challenge (teaser announcement)

The Fourth International NoCOUG SQL Challenge will be published in the May issue of the NoCOUG Journal which will be available at at 9 AM PDT on Monday, May 11 . Here's a sneak preview: How Romeo Won the Heart of Juliet Fourth International NoCOUG SQL Challenge Once upon a time, Romeo, the son of Montague, told his cousin Benvolio that he was in love with Rosaline but she was not returning (more...)

SQLcl – Code editing on the console

| May 1, 2015
We've been playing with our console drawing in SQLcl for a while now and this week, we hooked up some keys to make editing and running much easier.  The video will show the following keys for managing your buffer in the console.  This will make it into the next Early Access candidate soon.

  • up arrow - previous history (this will continue to show you the next history unless you move into the text to edit (more...)