APEX Application Development Service on Oracle Cloud

A few days ago Oracle announced the APEX Application Development Service on Oracle Cloud.

I had a sneak peak of this service last October. I’ve used APEX since it was know as Project Marvel, but despite this I’m renowned as the worst APEX developer on the planet. I think I was invited as a control subject… 🙂

The main thing I’m expecting from any platform is it must be as “administration free” as possible. APEX is a low-code development tool. Some APEX developers will have a lot of database administration skills, but some will not. I wanted to see a service that got people up and developing safely, with as little administration fuss as possible. I think that’s what Oracle have delivered, which makes me really happy.

These new APEX instances are built on Autonomous Transaction Processing (ATP) instances, with some of the functionality disabled. There is a simpler route to create them using the “APEX Application Development > APEX Instances” menu, but you can create them through the normal Autonomous Database creation screens also.

Existing ATP instances can be accessed through the simplified interface, which is a nice touch.

I did my own run through of setting up an APEX instance here. I’m not going to talk much about the service, because there are lots of great APEX bloggers that are a better source of information than me. Make sure you check them out!

Happy APEXing!



Video : JSON Data Type in Oracle Database 21c

In today’s video we demonstrate the JSON data type, introduced in Oracle database 21c.

The video is based on this article.

It assumes knowledge of existing JSON support in the Oracle database. If you are not familiar with the functionality Oracle have been including in the database since Oracle 12c, you might want to check out these.

The star of today’s video is my sister-in-law Maria Colgan. As if being related to me by marriage is not enough of a claim to fame, she is also the current reigning queen of the Oracle database. Long live the Queen!



Oracle Database 21c : It’s New to Everyone

A few days ago Oracle made the official announcement about the release of Oracle database 21c (here). This was a rather late announcement, as the product has been available on Oracle Cloud for over a month, but that’s not the subject of this post.

With each new release I feel the need to write a post like this, so here goes…


This is a new product, so there will be a lot of people in the community writing content about it, including me. The one thing common to all those people, including me, is none of us have any real experience of this version of the database. Very few, if any, will use this version in anger for a real production system for quite some time, if ever (see innovation release). Does that mean you should ignore the content being produced at the moment? No. You just have to understand that everyone is a newbie at the moment. It will take some time for people to not only understand the basics of the functionality, but also get a feel for its relevance in the real world. Just keep that in mind.

But what about the people that were using the new features in the 20c preview release on the cloud for a year? Yep. They are all beginners too. The preview release of 20c never became generally available, so nobody got something I would consider “production experience” on that release. Even the folks at Oracle don’t have significant production experience of 21c yet. It’s only been a month. Many of the 21c new features were initially showcased in the 20c preview, but things have changed since then, so it’s likely articles you are reading that were based on 20c are out of date and need revision.

I’m not saying all this to be negative. I guess part of it is a self-protection mechanism. People will be producing content now, and as they get more experience they will hopefully revise that content to reflect their current understanding. Don’t expect to find any 21c gurus for a long time… 🙂

When will we get 21c on-prem?

The current cloud-first release of 21c is 21.1. That will bump up to 21.2 with the January patches, and 21.3 with the April patches. If we look at what happened for 18c and 19c, both were released on-prem at version X.3 some time after the release of the April patches. It’s a fair guess to say something similar will happen for 21c.

Going back to the “production experience” argument, many people won’t even start their learning experience of 21c until the on-prem version drops, even though you can get a free-tier version of 21c on Oracle Cloud today!

21c is an innovation release

Another thing to consider is 21c is an innovation release. Many people will never use this version of the database. Instead they will wait for the next long term support (LTS) release, which is likely to be Oracle 23c. Many companies can’t cope with a yearly release cycle, and will prefer to jump between LTS releases. That means you could be on 19c for another 2.5 years before you get an opportunity to move to the next on-prem LTS release. In that time features we are writing about today may have evolved substantially.

Does that mean there’s no point learning about 21c now? No. The more you know about what’s coming, the more you can prepare for it. There will be a lot to learn between 19c and 23c, so it’s better to gradually soak it up as you go along. You may also see something that makes the jump to 21c worthwhile for a specific project. I know at least one project at work that will benefit from being at 21c ASAP.

19c is where you need to be now!

Your immediate focus should be to get all your systems on to 19c, and get them all migrated to the multitentant architecture. The non-CDB architecture was deprecated in, and is desupported (that means gone) in 21c onward. If you don’t get used to the multitenant architecture now, you are going to make life really hard for yourself when you move to 23c for long term support.

Now is the time to act.

Every upgrade is potentially unique, but I have some upgrade articles that might help.

If you are new to the multitenant architecture, I’ve written a lot about it, and have a playlist on my YouTube channel.

What next?

Get playing with 21c. Learn what you love. Avoid what you dislike. Most of all, make as much noise as possible, but try to keep it constructive. Most companies are a bit short sighted where their own products are concerned. They need external people to shake them out of their navel-gazing from time to time. That’s true of Oracle as well. My first few 21c articles have resulted in people from Oracle contacting me, then raising bugs/enhancements. There are plenty of people inside Oracle who want to engage and make their products better. Help them help us!



Video : Virtual Columns in Oracle Database 11g Onward

In today’s video we’ll demonstrate how to use virtual columns, introduced in Oracle 11g.

The video is based on this article.

The star of today’s video is Scott Wesley of APEX fame. I think he’s into gardening too, because he has a blog about grass or something… 🙂



Video : Convert CentOS to Oracle Linux

In today’s video we’ll demonstrate how to convert a CentOS installation to an Oracle Linux installation.

The video is based on this post.

The star of today’s video is Dan Norris, in a clip from a previous OpenWorld, which seems like a long distant memory now.



PS. I know I said there would be no more videos this year, but this was very much “of the moment”, so I thought it was worth doing.

Switching from CentOS 8 to Oracle Linux 8 (OL8)

If you’re a CentOS user, you’ve probably already seen Red Hat are ditching CentOS, and CentOS 8 will be the first casualty. At the time of writing Red Hat haven’t released a clear plan for what CentOS users should do. Neither Fedora or CentOS Stream are viable options for people looking for long term stability. There’s a suggestion that cut price RHEL licenses may be available in future, but all we know at this point is CentOS is on a road to nowhere.

One of the options you might want to consider is switching from CentOS 8 to Oracle Linux 8. Oracle Linux is a binary clone of RHEL, like CentOS. Also like CentOS, you can use Oracle Linux for free (downloads here), and that includes free updates.

But what do you do about your existing CentOS 8 installations? Well that’s really easy too, as you can convert them directly to Oracle Linux. Oracle have an overview of the process here, but it boils down to downloading a script from GitHub and running it. Here are the steps I used to do a conversion.

First, take a backup of the system, so you have a fallback restore point.

We display the contents of the “/etc/centos-release” file, which shows we have a CentOS 8.2 installation.

$ cat /etc/centos-release
CentOS Linux release 8.2.2004 (Core)

Download the conversion script from the Oracle GitHub repo, and run it. Then wait while it downloads the packaged as switches you from CentOS to Oracle Linux.

curl -O https://raw.githubusercontent.com/oracle/centos2ol/main/centos2ol.sh
sudo bash centos2ol.sh

Reboot the system.

sudo reboot

We now have an Oracle Linux server running the UEK kernel.

$ sudo cat /etc/oracle-release
Oracle Linux Server release 8.3
$ uname -r

If you don’t want to use the UEK kernel, you can switch to the Red Hat Compatibility Kernel really easily. List the current kernels.

$ ls -l /boot/vmlinuz-*
-rwxr-xr-x. 1 root root 8913656 Oct 22 22:59 /boot/vmlinuz-0-rescue-5fd85e2afa24422eb63894e2dbfa9898
-rwxr-xr-x. 1 root root 8975912 Dec 18 18:07 /boot/vmlinuz-0-rescue-caad1bd0b25943b1b526a131661074b3
-rwxr-xr-x. 1 root root 8920200 Sep 14 14:45 /boot/vmlinuz-4.18.0-193.19.1.el8_2.x86_64
-rwxr-xr-x. 1 root root 9520664 Dec 16 00:42 /boot/vmlinuz-4.18.0-240.8.1.el8_3.x86_64
-rwxr-xr-x. 1 root root 8975912 Dec 3 02:02 /boot/vmlinuz-5.4.17-2036.101.2.el8uek.x86_64

Set the latest Red Hat Compatibility Kernel as the default. It will be the highest version one without “uek” in the name.

$ sudo grubby --set-default /boot/vmlinuz-4.18.0-240.8.1.el8_3.x86_64
The default is /boot/loader/entries/caad1bd0b25943b1b526a131661074b3-4.18.0-240.8.1.el8_3.x86_64.conf with index 3 and kernel /boot/vmlinuz-4.18.0-240.8.1.el8_3.x86_64

Reboot the server.

$ sudo reboot

Now we are using the Red Hat Compatibility Kernel rather than UEK.

$ cat /etc/oracle-release
Oracle Linux Server release 8.3
$ uname -r


At this point we need to do some testing to make sure we are happy with the final result!

This type of switch may not be the preferred solution for every system, but it’s simple and saves you doing a full rebuild to switch to another distribution.

If you’re still not sure about Oracle Linux, here’s a FAQ post I wrote about it.



Video : Read-Only Partitions and Subpartitions in Oracle 12.2 Onward

In today’s video we’ll discuss read-only partitions and subpartitions, introduced in Oracle 12.2.

This is based on the following article.

You might find these useful too.

The star of today’s video is Chris Saxon of AskTom fame. You can check out his YouTube channel here.



Video : Invisible Indexes in Oracle Database 11g Onward

In today’s video we’ll discuss Invisible Indexes, introduced in Oracle 11g.

The video is based on this article.

The star of today’s video is Chris Muir, who was instrumental in me becoming a presenter. He invited me down to Australia to speak at a couple of AUSOUG events, which were the first proper conferences I ever presented at.



Video : Adaptive Cursor Sharing

In today’s video we’ll discuss the Adaptive Cursor Sharing feature, introduced in Oracle 11g Release 1.

This video is based on the following article.

Here are some other things you might want to check out.

The star of todays video is Franck Pachot, who is clearly looking for an exit. 🙂



Video : CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements

In today’s video we’ll discuss the CURSOR_SHARING parameter, which determines how the database handles statements containing literal values.

The video is based on this article.

You may also find these useful.

The star of today’s video is Toon Koppelaars from the Real World Performance Group at Oracle, and of #SmartDB fame. 🙂



PS. I’ve already noticed I say “after login”, when I mean “after logon”. 🙂