Oracle Scratchpad

December 21, 2020

Upgrade trivia

Filed under: 18c,Oracle,Upgrades — Jonathan Lewis @ 9:57 am GMT Dec 21,2020

Sometimes it’s the little things that catch you out (perhaps only briefly) on an upgrade. Here’s one that came up on Oracle’s Groundbreakers Developers Community [sic] (who knows what it will be called this time next year and, while we’re at it, who can tell where the apostrophe(s) ought to go).

The problem was with a database trigger that had been doing home-grown auditing to catch any DDL changes to non-SYS objects. The code was quite simple:

create or replace trigger system.audit_ddl_trg 
after ddl on database
        if (ora_sysevent='TRUNCATE') then

                null; -- I do not care about truncate

        elsif ora_dict_obj_owner!='SYS' then

                insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
                        sys_context('USERENV','OS_USER') ,
                        sys_context('USERENV','CURRENT_USER') ,
                        sys_context('USERENV','HOST') , 
                        sys_context('USERENV','TERMINAL') ,

        end if;

The issue was that after an upgrade from 12c (release not specified) to Oracle 19c the trigger was failing.

Here’s the definition for the table used by the trigger as the target of the insert statement – can you see any reasons why it might be failing:

create table audit_ddl (
        d               date,
        osuser          varchar2(255 byte),
        current_user    varchar2(255 byte),
        host            varchar2(255 byte),
        terminal        varchar2(255 byte),
        owner           varchar2(30 byte),
        type            varchar2(30 byte),
        name            varchar2(30 byte),
        sysevent        varchar2(30 byte)

If it’s not immediately obvious it’s probably because you’ve forgotten that object names (and various other identifiers) are allowed to be up to 128 characters in 19c (and a little earlier) – so defining the owner and name as varchar2(30) is an accident waiting to happen.

It didn’t take the user long to work out why there was a problem but the more interesting part of the issue was why there were now objects in the database with names exceeding the old 30 character limit. The OP supplied an (obfuscated) example: after the upgrade Oracle was reporting object names “using the full path name” like: “/some/path/name/object_name”.

The structure is a clue – for this user it’s all about Java classes. Here’s a little query against dba_objects with the results from and

select  object_name 
from    dba_objects 
where   object_type = 'JAVA CLASS' 
and     object_name like '%TimeZoneNamesBundle'


Java is a particularly enthusiastic user of long object names in Oracle – but it’s not the only culprit, there are a few others as we can see with another query against dba_objects – this time from 19c:

select  object_type, count(*)
from    dba_objects 
where   length(object_name) > 30 
group by object_type 
order by count(*)

OBJECT_TYPE               COUNT(*)
----------------------- ----------
PROCEDURE                        1
INDEX                            2
JAVA RESOURCE                 1286
SYNONYM                       4337
JAVA CLASS                   31739

If you’ve made much use of Java in the database before now you’re probably familiar with the call to dbms_java.long_name(). Since Oracle has a limit of 30 characters for identifiers it trims the leading edge (and sometimes a bit of the trailing edge) of the long names used by the public java libraries and uses a hashing function to create a short prefix. If you look in the sys.javasnm$ table (java short name?) in earlier versions of Oracle you’ll see that it has two columns – (short, longdbcs), and we can see the relationship between them:

select  short, longdbcs, dbms_java.longname(short) long_name 
from    javasnm$ 
where   rownum <= 10

SHORT                          LONGDBCS                                           LONG_NAME
------------------------------ -------------------------------------------------- --------------------------------------------------
/2ea59ec_TimeZoneNamesBundle   sun/util/resources/TimeZoneNamesBundle             sun/util/resources/TimeZoneNamesBundle
/8acf0d3a_OpenListResourceBund sun/util/resources/OpenListResourceBundle          sun/util/resources/OpenListResourceBundle
/e3e70b06_LocaleNamesBundle    sun/util/resources/LocaleNamesBundle               sun/util/resources/LocaleNamesBundle
/cc11c9d8_SerialVerFrame       sun/tools/serialver/SerialVerFrame                 sun/tools/serialver/SerialVerFrame
/1f9f2fa_N2AFilter             sun/tools/native2ascii/N2AFilter                   sun/tools/native2ascii/N2AFilter
/b6b3d680_UnsupportedEncodingE java/io/UnsupportedEncodingException               java/io/UnsupportedEncodingException
/7994ade2_CharsetEncoder       java/nio/charset/CharsetEncoder                    java/nio/charset/CharsetEncoder
/73841741_IllegalCharsetNameEx java/nio/charset/IllegalCharsetNameException       java/nio/charset/IllegalCharsetNameException
/f494d94e_UnsupportedCharsetEx java/nio/charset/UnsupportedCharsetException       java/nio/charset/UnsupportedCharsetException
/3092d940_MissingResourceExcep java/util/MissingResourceException                 java/util/MissingResourceException

10 rows selected.

With the appearance of long(er) identifiers in 18c, Oracle no longer needs to mess about with short names and a conversion function – it has just put the “fully qualified” name into obj$. I doubt if this will catch anyone out for long, but it might be nice to know about in advance.


  1. I recently had a somewhat similar issue after a 19c upgrade: The automatically-generated column names from PIVOT queries changed from 11.2 to 19.7 where the underlying column name that was too long got truncated differently.

    (I can go rummage up the specifics if interested, but you probably can whip up your own test case faster based on that description already, I imagine.)

    Comment by Jason Bucata — January 5, 2021 @ 7:51 am GMT Jan 5,2021 | Reply

    • Jason,

      Thanks for the comment – little things like this are sent to try us.
      Had to think for a minute about this one since I’ve hardly ever used pivot, but I got there in the end.
      About to write up a very short blog with the example.

      Jonathan Lewis

      Comment by Jonathan Lewis — February 1, 2021 @ 12:20 pm GMT Feb 1,2021 | Reply

  2. Hi,
    I don’t like too much varchar(N) columns. If things are implemented correctly no storage waste if N=max. A varchar(N) is a text with a length check but this check is often “too much” or “not enough”. In many cases it’s “too much” and if you expect a precise format it’s “not enough”. I usually migrate my varchar(N) columns to “character varying”. Max length of a character varying or a text is 1Gb with pg.
    With Oracle it’s even more complicated to use varchar(N) since N is not by default a number of characters but a number of bytes. It’s a problem with unicode.
    Best regards,

    Comment by Phil Florent — January 5, 2021 @ 11:20 am GMT Jan 5,2021 | Reply

    • Phil,

      It looks as if similar syntax has significantly different meaning. varchar2(N) in Oracle means varying up to a maximum of N; and for the removal of doubt you can also specify varchar2(N CHAR) to avoid thinking about multi-byte character sets. It’s also possible to set a parameter to tell Oracle that the semantics of declaration should always be CHAR rather than the default BYTE.

      Jonathan Lewis

      Comment by Jonathan Lewis — February 1, 2021 @ 11:55 am GMT Feb 1,2021 | Reply

  3. […] 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 […]

    Pingback by Pivot upgrade | Oracle Scratchpad — February 1, 2021 @ 1:35 pm GMT Feb 1,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by