Top 60 Oracle Blogs

Recent comments


DBMS_JOB and 19c – code changes needed

Here’s a “gotcha” brought to my attention by one of our AskTOM readers. I’ve mentioned in the past that DBMS_JOB, having been deprecated in favour of DBMS_SCHEDULER, got a new lease of life in 19c because under the covers we translated calls to create a job under DBMS_JOB to the same mechanism in DBMS_SCHEDULER.

The benefit of that is that we don’t need to maintain our older DBMS_JOB code base, but your existing code is fine to keep running. However, as I said in the other post, you do need to alter your privileges, but here is another discovery that might impact you as well.

How to upgrade to Oracle 19.8 on Windows

As always, you should follow the instructions in the README.html that comes with the Release Update, but because I know many people like to see what the experience of others is like, here’s my run of the patch application.

As always don’t forget to run your CMD prompt as Administrator, otherwise OPatch will typically have trouble accessing and locking the software inventory


Get your environment setup as per the patch notes.

Upgrading…Its Time!

Gough Whitlam was an Australian politician who rose to power in the 1970s with the campaign slogan “It’s Time!”. Politics aside, it loosely ran on the premise that not to have the occasional dramatic change ultimately leads to stagnation in social and economic progress.


MAX_STRING_SIZE – stretching the dictionary

This post cycles back too some other historical posts of mine related to max_string_size and the fact that the data dictionary uses LONG data type columns because of historical reasons related to backward compatibility. Most of us probably have existing databases that have gone through the standard upgrade through various versions of the Oracle Database, and as such, all of our existing database have a max_string_size of STANDARD. Thus to convert to the larger string size, we start by setting max_string_size to EXTENDED, run the appropriate scripts after shutting out database down and re-opening it in upgrade mode, setting the appropriate initialization parameters for the instance in our SPFILE. But what if you are creating a new database?

From 19.6 to 19.7 on Windows

I must say this Release Update (RU) was probably the smoothest I’ve ever done. Obviously you should always read the patch notes carefully before proceeding on your own systems, but for me, it was a simple exercise. I’m posting this just to cover a couple of things that the patch notes “assume” and don’t explicitly state.

  • Shutdown everything Oracle related. I just go to “Services” and look for anything with Oracle. Also shutdown the “Distributed Transaction Coordinator service”.

This next one is key … I’ve made this mistake so many times. Open a command prompt window as administrator. If you don’t, things will progress OK for a tiny bit and then OPatch is going to throw a wobbly.

I did both the 19.7 RU and the 19.7 OJVM with OPatch, and both went through without incident.

APEX 20 patch

I’ve been debugging an issue within an APEX application and wanted to make sure that my local installation was at the same version as So I logged on to my workspace and ran queries on APEX_RELEASE and APEX_PATCHES and discovered that there was a patch available for 20.1.

That patch is 30990551 and you can download it from the usual spot on My Oracle Support. None of the bugs listed in the “Fixed Bugs” helped me with my issue, because it turned out to be my own code Smile. But here is what is fixed in the patch:

APEX upgrade – don’t forget your CDN

I haven’t dug too deep into this one, so what follows may be just coincidental but I thought I would blog it out just in case it happens to anyone else.

While testing AskTOM as part of our Application Express upgrade to version 20.1, I got a bit of a shock when I fired up AskTOM (in a testing environment) after the upgrade. The screen was … well…. “slim”. Smile



18c versus 19c

I had someone say to me at an event recently: “We’re are going to upgrade to 18c, because 19c is new and is probably less stable”.

Let me sum up that sentiment simply: It’s Wrong Smile

Now, don’t get me wrong. I am not claiming that every Oracle release is perfect, contains zero bugs, never has a regression, will mow your lawn, take your kids to school, clean your house and sort out all the climate change issues in the world.

UTL_FILE_DIR and 18c revisited

A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on this more with an example of erasing your database with a short UTL_FILE script Smile)

DBMS_JOB – the joy of transactions

This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.

Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:

1) What about new jobs submitted via the old API after the upgrade?