Top 60 Oracle Blogs

Recent comments


Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan)

I previously discussed how Automatic Indexing only currently supports Equality based predicates. The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look. The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column: If we look at the current details of the table columns: […]

Learn ODA on Oracle Cloud

By Franck Pachot

You want to learn and practice your ODA command line and GUI without having an ODA at home? It should be possible to run the ODA image on VirtualBox but that’s probably a hard work as it is tied to the hardware. About the configuration, you can run the Oracle Appliance Manager Configurator on your laptop but I think it is not compatible with the latest odacli. However, for a long time Oracle provides an ODA simulator and it is now available in the Oracle Cloud Marketplace for free.

Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud)

There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle to make it worth a quick blog article. The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has […]


Here’s a quirky little detail – probably totally irrelevant to virtually everyone – that came up in a question on the Oracle Developer Forum a couple of days ago. It concerns the way Oracle stores and records default values for columns, and it also does a hat-tip to the “commas at the start/end of the line” argument. Here’s a little script to create two identical tables:

Pivot upgrade

I’ve hardly ever touched the pivot/unpivot feature in SQL, but a recent comment by Jason Bucata on a note I’d written about Java names and the effects of newer versions of Oracle allowing longer object and column names prompted me to look at a script I wrote several years ago for 11g.

As Jason pointed out, it’s another case where the output from a script might suffer some cosmetic changes because of an upgrade. Here’s the script to generate some data and run a query:

Video : JSON_TRANSFORM in Oracle Database 21c

In today’s video we demonstrate the JSON_TRANSFORM function, introduced in Oracle database 21c.

This video is based on the following article, which has a lot more information and examples than the video does.

I’ve written a bunch of stuff on JSON and ORDS, and there’s a YouTube playlist.

19c serverless logon trigger

By Franck Pachot

I thought I already blogged about this but can’t find it. So here it is, with a funny title. I like to rename oracle features by their user point of view (they are usually named from the oracle development point of view). This is about setting session parameters for Oracle connections, directly from the connection string, especially when it cannot be set in the application (code) or in the DB server (logon trigger).


Here is a simple example. I connect with the full connection string (you can put it in a tnsnames.ora of course to use a small alias instead):

Migrating Oracle Exadata Workloads to Azure- Storage Indexes

I’m about simplifying anything for customers as we bring over complex environments into Azure and Oracle databases running on Exadata is a big part of these challenges.  Decoupling the database from the engineered features is a crucial part of my work and with Oracle 19c, having customers running on the terminal release isn’t the only reason to upgrade if the database is on an earlier release.

As I’ve discussed in other posts, blogs and articles, I have numerous ways to address latency when losing cell node offloading, hybrid columnar compression (HCC), thin cloning with sparse clone, flash cache, flash logging, etc., but storage indexes are unique to Exadata that simply have no comparable work around.

Long Term Releases, Innovation Releases and Preview Releases?

I feel like this has been said a lot over the last year or so, but I still keep getting questions about it, so I’ll chip in.

Before we jump into it, the first place you should be looking for release information is this MOS note.

Release Schedule of Current Database Releases (Doc ID 742060.1)

With that in mind, here we go…

Long Term Releases (Long Term Support Releases)

I prefer the name Long Term Support (LTS) Releases, as that is really what they’re all about.

Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning)

  I was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in need of a couple of amendments. Initially when Automatic Indexing was released, the ability to set Advanced Compression was NOT included in the official documentation, although the EXEC DBMS_AUTO_INDEX.CONFIGURE( ‘AUTO_INDEX_COMPRESSION‘ […]