OT: Some advice for beginning Oracle Database professionals

And if you don’t know Which to Do Of all the things in front of you, Then what you’ll have when you are through Is just a mess without a clue … —Winnie the Pooh in The Tao of Pooh, by Benjamin Hoff Excerpts from the first chapter of my just-released book Beginning Oracle Database 12 c Administration : Thank you very much for buying this book (or for getting a legal copy). Database administrators (more...)


Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2

Conditional Compilation and Static Boolean

One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.

With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)

Mac SQL Developer Install

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite, as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the (more...)

Bash Arrays & Oracle

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about (more...)

Introduction to Oracle Database 12c

With the launch of DB 12c in 2013, Oracle introduced a new architectural concept, called “multitenant databases”, where you have one super database (=container; CDB) and one or more sub databases (= pluggable DBs; PDB).

Before running the installer on an Oracle Linux 6 environment, a library can be installed through yum to meet all the system per-requisites:

yum install oracle-rdbms-server-12cR1-preinstall

The software installer and DBCA are similar to 11g, except for this screen where (more...)

Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just (more...)

Leaf node queries

A reader posted A dynamic level limiting hierarchical query about Oracle’s hierarchical queries. They wanted to know how to capture only the hierarchy to the level where the first leaf node occurs. They gave me the following hierarchy map as an example:

               1                                    2
        +-------------+                       +-----------+
        |             |                       |           |      
        3             5                       4           6
    +---------+    +-----------+           +-----+    +------+
    |         |    |           |           |     |    |      |
    7         9    11          13          8     10   12     14
+-----+    (more...)

Refresh Multiple Materialized Views in One Go: No Data Found

To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).

The procedure that I initially wrote was the following:

create (more...)

OT: New Oracle Database features spell doom and gloom for NoSQL

Oracle Corporation has released a slew of new features that allow Oracle Database implementations to catch up to, match up to, and leave in the dust NoSQL implementations in the areas of performance, scalability, and reliability (PSR).(read more)

OT: Oracle wants your votes

It used to be that if you had an enhancement suggestion for Oracle Database, you had to file an enhancement request in MOS and pray that the Oracle product management gods would hear your request someday. It is now possible to make your enhancement request in a public forum and let the community vote on your idea.(read more)

OT: Never buy an Oracle book again (if you live in California)

If you are a resident of California, you can become a member of the San Jose Public Library and gain access to Safari Books Online and Books24x7. At last count, there were 445 Oracle titles available through Safari Books Online(read more)

Oracle Exadata Performance: Latest Improvements and Less Known Features

Here are the slides of a presentation I did at the IOUG Virtual Exadata conference in February. I’m explaining the basics of some new Oracle 12c things related to Exadata, plus current latest cellsrv improvements like Columnar Flash Cache and IO skipping for Min/Max retrieval using Storage Indexes:

Note that Christian Antognini and Roger MacNicol have written separate articles about some new features:



OTN Yathra 2015 just got over!!

Quite well said – Investment in knowledge is the gives you the best returns. It was a great fun last week to be the speaker at OTN Yathra 2015, organized by All India Oracle Users Group (AIOUG). The OTN tour covered seven cities and I was the part of it at three cities i.e. Hyderabad, Bangalore … Continue reading

Lets catch up at OTNYathra 2015

All, Its time to be at AIOUG stage once again and the event would be the OTNYathra 2015. The event has been a great success in the past and has been able to generate/receive wide recognition and appreciation. OTNYathra focuses to evangelize the Oracle technologies to a broader and passionate audience. OTNYathra is a grand tour which is conducted … Continue reading

ODI 12c 12.1.3 oci driver

When you connect from ODI Studio 12c to the repository you may get error “no ocijdbc in java.library.path”. In my case I use ODI Studio 12c installed on Windows 7 64bit machine and connecting to ODI 12c linux environment. So why do I get this error?




On the same note I use Oracle JDBC Driver ODI driver connection. As the studio does not use the tnsnames to connect to the Oracle (more...)

Oracle 12c: Temporal Validity, multiple on one table – Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that (more...)

Oracle In-Memory Column Store Internals – Part 1 – Which SIMD extensions are getting used?

This is the first entry in a series of random articles about some useful internals-to-know of the awesome Oracle Database In-Memory column store. I intend to write about Oracle’s IM stuff that’s not already covered somewhere else and also about some general CPU topics (that are well covered elsewhere, but not always so well known in the Oracle DBA/developer world).

Before going into further details, you might want to review the Part 0 of this series and also our (more...)

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of (more...)

About index range scans, disk re-reads and how your new car can go 600 miles per hour!

Despite the title, this is actually a technical post about Oracle, disk I/O and Exadata & Oracle In-Memory Database Option performance. Read on :)

If a car dealer tells you that this fancy new car on display goes 10 times (or 100 or 1000) faster than any of your previous ones, then either the salesman is lying or this new car is doing something radically different from all the old ones. You don’t just get orders of magnitude (more...)