Learning is not a spectator sport

Since 18c, Express Edition (XE) has typically been my “Go-To” release of the Oracle Database. It is free, quick to install, contains virtually no limitations on the features and options that you are permitted to use and has sufficient space for me to hold a clone of the AskTom schema so I can test AskTom application changes locally before pushing them into our genuine internal Oracle AskTom development environment here at Oracle.

Hence as you can imagine, I was thrilled to see 21c XE become available for Linux and Windows over the past couple of weeks. I can be “off the grid” and still tinker and explore the latest database features on my laptop.

But I had a couple of customers reach out with just a small snag…which is best summarised by on simple comment I got as a DM on social media:

“Er…um…but we’re on 19c like you told us to be“

Yes this is indeed true. 21c is an innovation release, and is primarily about presenting the latest features of the database either to exploit for business competitive advantage, or so that Developers and DBAs alike can be fully abreast of the latest tech offerings well in advance of their inclusion of the next long term support release (23c). But what of your existing day to day business application database environments? Those Production, UAT and Development environments are going to be on 19c, because it is the current long term support release, and you’ll most probably stay on 19c until 23c comes out in the 2023 timeframe.

This unfortunately creates a mismatch for developers. Your main development database will be running 19c, but your private sandbox (running XE) on your own machine either has to 18c XE or 21c XE. If you go with 18c, you can’t test 19c features that you’d like to incorporate into your applications without doing it directly against your main development environment, but if you go with 21c then how do you ensure that the features/behaviours you are observing in your 21c sandbox will align with your 19c database when you merge your builds back into the main database? Neither situation is ideal.

There isn’t a 19c XE but we can get pretty close. Here’s something I’ve been tinkering with – it is obviously not supported, but with XE being a free tool, you aren’t heading into support territory anyway.

Step 1 – Create a new template

From your existing XE installation, fire up Database Creation Assistant and create a template from your existing XE database.

De-select the “Include Datafiles” because we want to build our new custom XE instance from scratch.

Step 2 – Delete your existing XE database.

Clearly, make sure you have extracted any schemas/data/etc before doing this. You can only have a single XE running on a machine, and we are going to create a new one, so the current one has to go.

Step 3 – Edit the custom template

In this template, because we are no longer including the datafiles, we can control the compatible parameter. We can set this to whatever we want, but assuming we’re matching to a 19c environment, I’ve set mine to 19.0 which means no blockchain or immutable tables will be possible, even though this is a 21c instance. I could also set things like optimizer features etc to get as close to a “19c” experience as possible.

Step 4 – create a new XE database based on your template

Fire up Database Creation Assistant again, and create a new database based on your new custom template.

Note that the database must be called XE which is burnt into the checks we do for Express Edition. This first creation will take a while because we are building the database dictionary and options from scratch,

but once complete, you could capture a fresh template from it and this time you can include the datafiles should you wish to pass this template around to other developers on your team.

And there we go … A “19c” Express Edition database to align with your 19c development environment. Now every developer in your organisation can tinker with their own private database, whilst still keeping themselves closely aligned to the likely long term support versions you’re running in your data centre.

A database on every laptop means every developer can now be an advocate for building great database-centric applications – something Joel would have been proud of. RIP.