Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

Todays story is about, how dynamic sampling can effect your plan stability during upgrade and how you can recover from it as long as you know what your are doing.

A little bit of background, I’m currently in the middle of migrate and upgrade cycle for 12TB warehouse from Solaris to (yet) Linux.
Plan stability on this system is maintained by locking the main driving table statistics and hinting the queries in case CBO does not pick the right driving table.
The system uses temporary tables a lot for staging operations and (more...)

WLST Script changing logfile location

While I was migrating Forms6i to Forms11g patch set #5, the configuration tool of Forms11g is a bit strict. In the silent install it is not possible to set de locations of the log files. Here is the script that will set new filename locations of all the Managed Servers (more...)

Quick Hadoop 2-node Cluster Setup for Oracle DBAs

OS Version: Red Hat Enterprise Linux Server release 5.6 
Hadoop Version: 1.0.1
Hosts: host1, host2

Step 1: Install Java on the Hosts

yum install java-1.6.0-openjdk-devel.x86_64

Check for Java version
java -version

Step 2: Create OS Group and User on hosts

Add Group:
/usr/sbin/groupadd hdfs

Add User:
/usr/sbin/useradd -G hdfs -d /home/hadoop hadoop

Change user password:
passwd hadoop

Step 3: Setup password less ssh between the hadoop users on both (more...)

Cloud-Based Marketplaces and Services

The cloud is clearly where the world is moving!  Amazon has done an amazing job of offering up cloud-based infrastructure services (i.e. servers by the hour).

There are thousands of DVDs on the market today.  We all know that DVDs are going away.  Everyone is watching movies on their iPads and iPhones now.  You see kids watching movies at restaurants on iPhones.  Roku is your future cable killer.  It allows you to watch online content on your TV.  It's similar to Apple TV in many regards.

My new company, InteliVideo has built a cloud-based platform that helps those companies (more...)

Google-like Search and LOVs using Oracle Text and ViewCriteria

Do you know the Google Advanced Search page? Very few people have ever seen the page, let alone used it. Now, why is that? It is simply because the "normal" single-field quick-search (that what we normally just call Google) is so incredibly effective.

OK, that's nice, but how can (more...)

How to Align Managed Bean Scope and Bean Data Control in Oracle ADF

Bounded task flows are natural building blocks of modular Oracle ADF applications. A bounded task flow facilitates the isolation of specific implementation details in its own memory scope - a page flow scope. For example, a managed bean  in pageFlow scope has a lifespan of its bounded flow. The other commonly used (more...)

Understanding Linux Load Average – Part 3

In part 1 we performed a series of experiments to explore the relation between CPU utilization and Linux load average. We concluded that the load average is influenced by processes running on or waiting for the CPU. Based on experiments in part 2 we came to the conclusion that processes that are performing disk I/O [...]

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

Don’t be evil Google!


In the last 10 weeks suddenly I lost 80% of my blog visits… thanks to google! In the last 6 months I found dozens of copycats of posts I wrote and those sites have a greater rank than my legit original blog!

For that, Thank you a lot google… you’re (more...)

Setup Data replication from Oracle to MongoDB


Components used:
MongoDB: MongoDB shell version: 2.0.5
Oracle Advanced Queuing
Perl scripting
DBD:Oracle: perl DBD for Oracle
JSON perl module.

The model for this setup is:
Replicate HR.EMPLOYEES table from Oracle to MongoDB.
Use Oracle AQ to queue insert, update & delete and then use perl script to dequeue them in MongoDB.

Step 1:
Use SCOTT.EMP table available in the SCOTT schema in (more...)

MongoDB Install and Quick Setup

1.) Download MongoDB from -
Copy is over to your host, unzip it.
Thats all to install Mongo DB.

2.) Create directory for storing your data

mkdir /home/oracle/data
You can create this directory anywhere on the host/filer/shared storage.

3.) Start mongoDB (

cd to bin directory inside the downloaded/uncompress mongoDB software.
cd /home/oracle/mongodb/bin

run ./mongod

By default it will start the mongoDB on port 27017.



A week ago I was back home from MEDIAS-2012 conference. It was held in Limassol (Cyprus), near the spectacular ruins of ancient city Amathus. This was unique style general Computer Science conference with speakers including legendary Soviet cosmonaut Alexandr Serebrov and the inventor of Mean Value Analysis Professor Martin Reiser. In my experience the Reiser’s [...]

Tracking the cluster node of BPEL instance invocations in SOA / AIA 10g

In a BPEL 10g cluster you often have the situation that incoming messages are picked up by adapters (i.e. JMSAdapter) on all nodes in parallel. Verifying if all nodes pickup these messages evenly is not easy in 10g because the processing cluster node is not saved in the DB (more...)

Strange ORA-14404, or not?

I was trying to drop a tablespace which I know there were no segments in it. A simple query from dba_segments returns no rows which means there are no segments allocated in this tablespace. But strangely I got this:

SQL> drop tablespace psapsr3old including contents and datafiles;
drop tablespace psapsr3old including contents and datafiles
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

How come I cannot drop a tablespace with no segments in it?

Enter deferred segment creation. Things were simpler on 9i or 10g. The database I get this error on is an (more...)

How to display Oracle PL/SQL "OUT" variable in perl

Step 1: Create a procedure in oracle with OUT variable

create procedure testing (timendate out varchar2)
select systimestamp into timendate from dual;

Note: Above proc outputs systimestamp.

Step 2: Test the proc output in Oracle

set serveroutput on
time varchar2(200);
dbms_output.put_line('Returned: ' ||time);

The output should be like:
Returned: 23-MAY-12 PM -07:00

PL/SQL procedure successfully completed.

Step 3: Get this output (more...)

The Girl With The ANSI Tattoo

I enjoyed the David Fincher remake of The Girl With The Dragon Tattoo more than I thought I would. Rather than a shallow and cynical Hollywood cash-in, it's actually a tense, atmospheric, only slightly voyeuristic crime thriller. My favourite part, though, was when Lisbeth Salander begins to solve a 40 year old murder cold case using SQL.


We see her tapping at her laptop as she hacks effortlessly into the Swedish police database, interspersed with green-tinted tracking shots of scrolling text as she types in keywords like 'unsolved' and 'decapitation', though never quite the whole query:

[girl_tattoo1.jpg] [girl_tattoo2.jpg]

Naturally I couldn't help (more...)

Next-Generation Oracle Mobile Applications

In addition to my FatWire tutorial talk, I gave one on ADF Mobile. Or, more accurately, I talked about how cool it is that Oracle is going to be bundling PhoneGap with their ADF Mobile toolkits!

I was never really a fan of mobile applications: I prefer the mobile web experience. Every mobile device supports HTML5, which means that you can do just about everything a mobile app can do, other than high-performance graphics. In fact, according to an Adobe study users prefer mobile web to mobile apps for just about everything.

Introduction to Oracle OLAP Web Presentation Series

I've posted a series of three videos introducing Oracle OLAP.  This is a great series for people how are interested in learning about what Oracle OLAP is and what it's used for.  I suggest starting viewing these in order.  Here are the links:

Oracle OLAP Overview:  Part (more...)

Serial direct read for small tables in

Today I have fixed an issue related with serial direct path reads.

There is database

SQL> select * from v$version where rownum = 1;
Oracle Database 11g Enterprise Edition Release - 64bit Production
SQL> SELECT a.ksppinm  "Parameter",
  2         b.ksppstvl "Session Value",
  3         c.ksppstvl "Instance Value"
  4    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  5   WHERE a.indx = b.indx
  6     AND a.indx = c.indx
  7     AND a.ksppinm = '_serial_direct_read';
Parameter               Session Value           Instance Value
----------------------- ----------------------- -----------------------
_serial_direct_read     auto                    auto

There is (more...)

SQL*Plus Variable defined by default

Earlier i used to maintain different sql script files for one function for every database version. As some of the older release dont have some of columns available in new release’s view/tables. I started updating my scripts to maintain a single script using Tanel Poder’s  snapper script’s idea of using SQL*Plus variables to automatically comment out the version specific code in the script (if its not applicable for logged in database version), so that it does not generate the syntax error.

So initialize the variables (_IF_ORA_9iR2_OR_HIGHER, _IF_ORA_10gR1_OR_HIGHER etc) with single line comment “–” and if version of logged in database (more...)