Top 60 Oracle Blogs

Recent comments


Hotsos Symposium 2018

Just a quick note to say I’m doing a couple of talks at this year’s Hotsos Symposium, which starts next Monday at 8:30AM. It’s not too late to register.

As an enticement – here is one of my favorite pictures from last year’s event: (Come join the fun!)

IMG_0379 225w" sizes="(max-width: 480px) 100vw, 480px" />

255 Columns

It’s the gift that keeps on giving – no matter how many problems you find there are always more waiting to be found. It’s been some time since I last wrote about tables with more than 255 columns, and I said then that there was more to come. In the last article I described how adding a few columns to a table, or updating a trailing column in a way that made the table’s used column count exceed 255, could result in some strange row-splitting behaviour – in this article I’m going to look at a critical side effect of that behaviour.

18c dbms_xplan note about failed SQL Plan Baseline

SQL Plan Baselines is a great feature for plan stability: you capture the plans that you accept. However, if the data model changes and the accepted plans cannot reproduce, the optimizer will come with a new plan. In 18c we have a note from DBMS_XPLAN when the optimization ‘failed to use SQL plan baseline for this statement’.

I create a table, with an index, and run a query on it using this index:

SQL> create table DEMO as select rownum n from xmltable('1 to 1000');
Table DEMO created.
SQL> create index DEMO_N on DEMO(n);
Index DEMO_N created.
SQL> select * from DEMO where n=1;

A look into Oracle redo, part 5: the log writer writing

This the the fifth blog in a series of blogposts about Oracle database redo. The previous blog looked into the ‘null write’ (kcrfw_do_null_write actually) function inside kcrfw_redo_write_driver, which does housekeeping like updating SCNs and posting processes if needed, this blog looks into what happens when the log writer is actually posted by a process or if public redo strand buffers have been written into. In part 3 of this blog series (the log writer working cycle) it can be seen that when a session posts the log writer, it returns from the semaphore related functions, and calls ‘kcrfw_redo_write_driver’ directly, which otherwise is called inside ksbcti.

Inside the kcrfw_redo_write_driver function, the first thing of interest is executed only when the logwriter is posted, and the kcrfw_redo_write_driver function is called directly after returning from ksarcv and ksl_exit_main_loop_wait:

Duplicating a Database on ODA


Every so often, we get a request to duplicate a database for one of our customers using an Oracle Database Appliance (ODA). The process for doing that is relatively straightforward, but there are a couple of nuances along the way so I thought I’d write it up as a blog post in case it’s of use to others. Obviously, I have obfuscated any customer-specific information to protect their identity.


The first nuance is to understand what database is being used as the source for the clone. Generally, a request for cloning will be something like this:

“We are creating a new environment which needs a new database to be set up. Please copy P1_SRV_T and restore as P1_SRV_F”.

18c, Cloud First and Cloud Only features: think differently

Remember the times when the Oracle Software features were the same on all platforms? Where Oracle databases could be ported to any relevant platform? Where we were able to try any feature, freely, by downloading the latest release software? Now we need to think differently. Because:


In the spirit of Cary Millsap’s comment: “The fastest way to do anything is to not do it at all”, here’s my take (possibly not an original one) on solving problems:

“The best time to solve a problem is before it has happened.”

I spend quite a lot of my “non-contact” time thinking about boundary cases, feature collisions, contention issues, and any other things that could go wrong when you start to implement real systems with (new) Oracle features. The benefit of doing this, of course, is that when I’m looking at a client’s system I can often solve problems because I recognise symptoms that I’ve previously created “in the lab”. The strange thing about this is that there have been times when I’ve pushed Oracle to a breaking point, documented it, and then dismissed the threat because “no one would do that in real life” only to find that someone has done it in real life.

A free persistent Google Cloud service with Oracle XE

In a previous post I’ve listed several free online services which run an Oracle XE so that you can test your SQL easily. You may want use Oracle XE further, with full access to the database and its host, and still from a web browser. You probably have a Google account. Then you also have a Virtual Machine on the Google Cloud (0.5 vCPU / 1.70 GB RAM boostable to 1 vCPU / 3.75 GB) and 5 GB of persistent storage (as long as you used it in the 120 previous days). Just try this Google Cloud Shell:
In this post, I explain how to install Oracle XE there.

PDB upgrade from 12c to 18c

Oracle 18c is out, in the Oracle Cloud, and the first thing I do with a new version is testing how long it takes to upgrade a previous version PDB by unplug/plug. Faster upgrade should be the benefit of having a slim dictionary where the system objects are reduced to metadata links and data links. However, it looks like upgrading the PDB dictionary still takes the same time as upgrading the CDB$ROOT.

ODA Lite: What is this ‘odacli’ repository?

When ODA Lite was introduced, with ODA X6-2 S/M/L, and now with ODA x7-2 S/M, a new ‘odacli’ was there to manage it. It will probably replace the oakcli for ODA HA as well in the future. One big difference: it uses a repository to record the configuration and the operations. I don’t really like it because when something fails you are blocked. Oracle Support can modify the directory, but they ask for an access to the machine for that and this is not easy in secured environments. Anyway, I really don’t understand why another repository has been introduced. We already have the Oracle Inventory, the Grid Infrastructure resources, the Linux /etc files,… And now we have a closed repository which controls everything, accessible only with the very limited odacli commands which are not the best example of automation code and error handling.