Major and Minor keys in Oracle NoSQL Databsae

Oracle NoSQL Database uses Major and Minor key values to influence record co-location. Records are stored based on the hash of the major key, so all of the records with the same major key will be co-located on the same server.

A JSON document could be divided into sub-parts, with each sub-part having a different minor key. For example, customer meta data could be stored in the main customer record, transactions in a different record, (more...)

Oracle Cloud v/s Amazon Cloud?

A few years ago, I taught an online class in Oracle database administration for the University of Washington. Every student was given their own virtual machine for the duration of the class, courtesy of Amazon. It was ridiculously simple to clone, start, stop, and destroy virtual machines using the Amazon CLI (command line interface). All students had full SSH and SQL*Net access to their virtual machines in the Amazon cloud. At that time, Oracle had (more...)

Announcing the Dodeca Spreadsheet Management System, Version 7 and the Dodeca Excel Add-In for Essbase

After 18 months of hard work, Applied OLAP is proud to announce the general availability of the Dodeca Spreadsheet Management System, version 7, and the all-new Dodeca Excel Add-In for Essbase.

The Dodeca Spreadsheet Management System provides customers the ability to automate spreadsheet functionality, reducing the risk of spreadsheet errors while increasing productivity.  It combines unprecedented ease-of-use for business users using spreadsheets for planning, budgeting, forecasting, reporting and analysis tasks.  It also provides a robust, (more...)

Linux BPF/bcc for Oracle Tracing

Topic: In this post you will find a short discussion and pointers to the code of a few sample scripts that I have written using Linux BPF/bcc and uprobes for Oracle tracing.

Previous work and motivations

Tools for dynamic tracing are very useful for troubleshooting and internals investigations of Oracle workloads. Dynamic tracing probes on the OS/kernel, can be used to measure the details for I/O latency for example. Moreover probes on the Oracle (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

EM Express Login – Get Flash

I have 12c setup on OEL 6.7 on my VM and while trying to access EM express, I was receiving the error


Just for the notes, a user can log into EM Express using URL


and port details can be found using

lsnrctl status | grep HTTP
select dbms_xdb_config.getHttpsPort() from dual;

Clicking on the “Get Flash” takes you to abode flash page from where you can download the rpm for flash.


How to See When an Oracle Role Was Created

You can get the date and time a role was created from the CTIME column in SYS.USER$. You can see what I mean in the example below, which I tested in an Oracle database. First I created a role between DATE_AND_TIME1 and DATE_AND_TIME2. As they were the same, to the nearest second, the role must have been created at 16:11:05 on 27th May 2016.

I then waited 5 seconds and (more...)

Oracle AppsUnlimited – Migrate from On-Premise to Oracle Cloud ( Lift and Shift )

Before you read : Please note the documentation and procedures for Lifting and Shifting On-Premise to Oracle Cloud is constantly evolving. Instruction here are high level and recommend to review the latest documentation.

Link : Migrating an Existing Oracle E-Business Suite Installation to the Oracle Compute Cloud Service

Migrate from On-Premise to Oracle Cloud ( Lift and Shift )

On-Premise Environment 12.1.3 or 12.2 can be moved to cloud , please review (more...)

Unified Auditing – some insights

Oracle 12c Unified Auditing is a brand new feature in the latest Oracle version which consolidates database level auditing records into a single location. DBAs can access the audit information from the view UNIFIED_AUDIT_TRAIL for all kind audit records, and they are

SQL&gt; select distinct COMPONENT from all_unified_audit_actions;

Direct path API
Database Vault
Label Security

8 rows selected.

UNIFIED_AUDIT_TRAIL is a view owned by SYS and a public (more...)

Fixed Table x$ktfbue has not statistics

While playing around db, I ran my usual script to check tablespace usage details and it took ~59 secs (Elapsed: 00:00:59.02), which was high.
Ran the sql using “gather_plan_statistics” hint and below lines in the execution plan were of interest –

|  34 |      HASH GROUP BY                     |                    |      1 |      6 |      3 |00:00:58.68 |     129K|  54801 |  1160K|  1160K|  758K (0)|
|  35 |       VIEW                             | DBA_FREE_SPACE      (more...)

clckwrk Quick Guide: Oracle DBA – Managing the Alert Log on AWS

At clckwrk we managed Oracle on the AWS Cloud. First rule of being a DBA - check the alert log.

The database alert log is the file where the database logs core activity and any messages that the DBA needs to understand what the database is doing, any tuning that needs to happen at the database level and any errors that the database is producing. It's the errors that are critical for us as they (more...)

I/O Resource Management on Exadata

Consolidation is a key enabler for Oracle database deployments on both public and private clouds. Consolidation reduces the overall IT costs by optimizing the operational and capital expenses. In addition, it enhances the effective utilization of cloud resources. The Exadata database machine has been optimized to run schemas and databases with mixed workloads together, making … Continue reading


Why you might want to think twice about using INSERT ALL.

One of those things I knew and then forgot.

So, let’s say you’ve got three tables or a partitioned table or something like that.

Let’s use regional tables for simplicity.

drop table t1_r1;
drop table t1_r2;
drop table t1_r3;

create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));

create table t1_r2
(col1 varchar2(2) not null
,col2 number not  (more...)

Dropping constraint… what about the index?

In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.

Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:

create table test_explicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

create unique index col1_exp_pk on test_explicit_index (col1);

We can see the (more...)

My "Must See" ADF/MAF Sessions at KScope 16

Yes, you read that right - it's not a typo, nor did one of my kids or wife gain access to my laptop.  It's part of a "blog hop" - where a number of experts made recommendations about KScope sessions that are "must attend" and are not in their core technology.  I picked ADF/MAF, as I don't have any practical experience in either technology, but they are at least similar enough that I would not (more...)

The river floes break in spring…

Alexander Blok
The river floes break in spring...
March 1902
translation by Greg Pavlik 

The river floes break in spring,
And for the dead I feel no sorrow -
Toward new summits I am rising,
Forgetting crevasses of past striving,
I see the blue horizon of tomorrow.

What regret, in fire and smoke,
What agony of Aaron’s rod,
With each hour, with each stroke -
Or instead - the heavens’ gift stoked,
From the (more...)

Changing the page title in Fluid at run-time

One of our customers asked us to implement nested landing pages, in which some tiles would open a second landing page and eventually a third one and so on. Let me illustrate the use case with some screenshots (I apologise as they are in Spanish, but it should be useful anyway). This would be how the main landing page would look like:

By clicking the "Formación y Desarrollo" tile, a new landing page will be (more...)

HIUG Interact 2016 Agenda

In a couple of weeks, I will be presenting the following sessions at the HIUG Interact 2016 conference in San Antonio

  • 16165 : PeopleSoft Fluid User Interface – Deep Dive: Grand Oaks D, Mon, Jun 13, 2016 (03:15 PM - 04:15 PM)
  • 16164 : PeopleTools Tips & Techniques: Grand Oaks D, Tue, Jun 14, 2016 (02:30 PM - 03:30 PM)
  • 16163 : Tech Clinic: Application Designer Grand Oaks D, Wed, Jun 15, 2016 (12:30 PM (more...)

Oracle PL/SQL Developer Certified Professional

Today I passed my exam for Oracle PL/SQL Developer Certified Professional(OCP)... Hurray!

“What do you mean there’s line breaks in the address?” said SQLLDR

I had a large-ish CSV to load and a problem: line breaks inside some of the delimited fields.

Like these two records:

one, two, "three beans", four
five, six, "seven
beans", "eight wonderful beans"

SQL Loader simply won’t handle this, as plenty of sad forum posts attest. The file needs pre-processing and here is a little python script to do it, adapted from Jmoreland91’s solution on Stack Overflow.

import sys, csv, os