OBI formatting grand totals – without XML!

Recently I needed to have some conditionally formated grand total rows in OBI. I remember years ago hacking around with the XML which is far from ideal. I then realised it could be done another way, here is the example:   Create an analysis with a measure column: Apply the conditional format on the Actual

Getting Smarter in Renting with Tableau 10


Not a long time ago a friend of mine spent a significant amount of time trying to find a flat to rent. And according to what he said it wasn't an easy task. It took him a decent time and efforts to find something that is big enough (but not too big) not too far from a workplace, had required features and affordable at the same time. And as a specialist in data analysis, (more...)

dbca – db creation stops with dbmssml.sql error


Trying to create a database using dbca on a Oracle Linux 7 running in a Virtualbox Container. DBCA throws an error during database creation: something like "dbmssml.sql. refer to dbmssml0.log error in process: perl". The mentioned log file is empty.


Important!: It is NOT a database software or a dbca problem. Additionally, if You run a 'perl -v' command, You'll receive a 'segmentation error' - this is, too, not (more...)

Things to do after you cloned a Virtual Machine

Clean up a cloned VM After you made a clone of your (base) VM, you will need to do some stuff. MAC-address First of all, I suspect you have a different MAC-address than the original machine. VMWare does that, as long as you have your MAC address assigned automatically. VirtualBox will ask you whether to re-initialize the MAC-address while cloning. The problem is the udev process, responsable

Steps for Moving ASM Disk from FRA to DATA

Due to some unexpected data load, the space in DATA diskgroup became critically low on one of the production systems during middle of night on the weekend. There was no time to get a new disk and we needed the space to make room for new data load scheduled to be run after 3 hours.

Looked at the tablespaces space in DATA diskgroup and there wasn't much hope in terms of moving or shrinking or (more...)

It’s just bad code or bad design … most of the time

Some years ago I wrote an article for the UKOUG magazine called “Want a faster database – Take a drive on the M25”.  For those not familiar with the United Kingdom, the M25 is one of its busiest roads (M = “motorway”) and because it moves so much traffic, and runs so close to capacity, it has often been referred to as “the world’s largest car park”.  Many people have probably spent a (more...)

The river floes break in spring… take 2

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,
the lament of the cross,
With each hour, with each stroke -
Or instead - the heavens’ gift stoked,
from the bush (more...)

Bash: The most useless command (4)

The blog statistics show, that there are many people reading the posts about useless commands. And there is the next candidate:
Now you are thinking, what is cowsay?
       Cowsay  generates  an  ASCII picture of a cow saying something provided by the user.  If run with no arguments, it accepts standard input, word-wraps
       the message given at about 40 columns, and prints the cow saying the given message (more...)

Oracle SQL Strip Quotes

Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:

SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message"
FROM   dual;

It will print:

Hello World!

As always, I hope this helps those looking for a solution.

Birth of a user group conference…

Or, how post-conference blues hit.


You don’t actually get to witness these kinds of things to often. Yes, there was the birth of POUG conference, the passionate work of Kamil Stawiarski and the people he gathered around him. He did an awesome job and pulled it off.
Why then is this so special? Well, first of all because it is my native conference. People that have contributed for many years, working closely together (more...)

Now, here’s an idea…

Gaining control Or rather - regaining control. Over my own data, and what's done with it. Currently, I use several services, of which I know they are monitored. Several of these services fall under US legislation, although I'm not a US citizen. This allows several agencies to go through my documents, email and other stuff, whether I like that or not (I do not). Of course, for some of this, I

Stroage server

Storage server Hardware Aiming at 2 VDEVs of 5 or 6 disks each, I'd need a motherboard capable of running 12 disks.I used a SuperMicro board in the ESXi build, mainly because virtualization using bare metal hypervisors was quite new to me. However, these boards have quite a steep price. There's a new motherboard by SuperMicro, that screams NAS, but that has not yet hit the shops. So, I

Write (Even) Less with More – VALIDATE_CONVERSION

I wrote the post Write Less with More – Part 8 – PL/SQL in the WITH Clause in November 2015, when the latest released Oracle version was 12.1.
In that post I explained about PL/SQL in the WITH Clause – a new 12.1 feature – and demonstrated it using the following example:


Since then Oracle 12.2 was released, and introduced a new feature that enables solving this task in a simpler way – the VALIDATE_CONVERSION function. This function gets an expression and a data type, and returns 1 if the expression can be converted to the data type and 0 if not.
Using the same setup from the original post, the requested query becomes as simple as:

> select *
  from   people
  where  general_info is not null
  and    validate_conversion(general_info as date, 'dd/mm/yyyy') = 1;

---------- ---------- --------------- --------------------
       102 Paul       McCartney       18/6/1942
       202 Ella       Fitzgerald      15/6/1996
       203 Etta       James           20/1/2012

In addition to introducing the new VALIDATE_CONVERSION function, the older CAST and some of the TO_* conversion functions have been enhanced in Oracle 12.2 and include a DEFAULT ON CONVERSION ERROR clause, so when data type conversion fails we can get some default value instead of an error.

> select p.person_id,
         to_date(p.general_info default null on conversion error, 'dd/mm/yyyy') my_date
  from   people p;

---------- ---------- --------------- ----------
       101 John       Lennon
       102 Paul       McCartney       18/06/1942
       103 Ringo      Starr
       104 George     Harisson
       201 Louis      Armstrong
       202 Ella       Fitzgerald      15/06/1996
       203 Etta       James           20/01/2012
       317 Julie      Andrews

8 rows selected.

The post Write (Even) Less with More – VALIDATE_CONVERSION appeared first on @DBoriented.

OGh Tech Experience 2017 : The Journey Home

I woke up really early so I decided to head straight for the airport. It was a 20 minute bus ride to the station, then a train to the airport, or so I thought. It turned out there wasn’t a straight through train, so I had to go via Utrecht. Once at Utrecht I got on a train the the airport, but it stopped early due to work on the lines, so I had to (more...)

Avoiding Lost Updates with ORA_ROWSCN

Applications today use "optimistic locking" to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn't work (see ORA_ROWSCN for optimistic locking). I now think it does!

OGh Tech Experience 2017 : Day 2

I woke up on day 2 feeling pretty dreadful, but I lay in the bath for a while, waiting for a dose of Lemsip to kick in. By the time I got out of the bath I was feeling OK-ish.

By the time I finished the 20 minute bus ride and the short walk to the venue I wasn’t feeling so great, but the adrenalin started to kick in, which always helps. I sat for (more...)

Modified PythonDBAGraphs to use datetime on X axis

I modified PythonDBAGraphs to use datetime objects on the X axis for all the reports except This lets you hover the mouse over a point and see the date and time for the point. Previously I was using text strings as labels with dates and times on the X axis and it was not obvious which point was for which date and time.

Here is a screenshot:

I have an example of a (more...)

12c DataGuard Validate and More

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique (more...)

JDBC, Linux and Entropy

Some troubles — especially those happening only sporadically — are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system, particularly random number generation. This case was suited well to demonstrate an approach to trouble-shoot […]

nl.OUG Tech Experience 16 June 2017

Today the second day of the nl.OUG Tech Experience took place in ‘De Rijtuigenloods’. After a really interesting first day it was a real challenge to be at least as good as the day before on day 2. Also for this day the Dutch Oracle User Group has prepared an interesting and varied program. It … Continue reading "nl.OUG Tech Experience 16 June 2017"