AUDIT DDL trigger / table causing issues

FBP_FLUOR

i have a newly upgraded 19c db from 12c. It has a table, SYSTEM.AUDIT_DDL which is:

CREATE TABLE SYSTEM.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)

)

TABLESPACE TRKP_DDL_MONITOR_TS

PCTUSED  0

PCTFREE  10

INITRANS  1

MAXTRANS  255

STORAGE  (

      INITIAL     20M

      NEXT       1M

      MINEXTENTS    1

      MAXEXTENTS    UNLIMITED

      PCTINCREASE   0

      BUFFER_POOL   DEFAULT

      )

LOGGING 

NOCOMPRESS 

NOCACHE;

and a trigger to insert into the AUDIT_DDL table,

CREATE OR REPLACE TRIGGER SYSTEM.AUDIT_DDL_TRG after ddl on database

begin

 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)

  values(

   sysdate,

   sys_context('USERENV','OS_USER') ,

   sys_context('USERENV','CURRENT_USER') ,

   sys_context('USERENV','HOST') , 

   sys_context('USERENV','TERMINAL') ,

   ora_dict_obj_owner,

   ora_dict_obj_type,

   ora_dict_obj_name,

   ora_sysevent

  );

 end if;

end;

/

problem is on my old db it would insert the name of an object like

NAME: ObjName

and on the new on it inserts the full path like

/some/server/path/ObjName

and the new way is causing things to fail because the new "name" is longer than the allowed 30 Bytes that table will hold for NAME.

i can update the table to make the name column longer, sure, but why is this difference appearing? I feel like i may be missing something simple here. Thanks in advance for your answers! Have a great day!

Jonathan Lewis

That structure looks like you're capturing the names of objects of type JAVA CLASS. Maybe they weren't being captured in 12c, or maybe the internal naming convention was different, but if you look in all_objects or dba_objects you'll see that objects of type JAVA CLASS can have names much longer than 30 characters - and the column is defined as varchar2(128).

For comparison I see in an 11g instance object_name: "/2ea59ec_TimeZoneNamesBundle" which probably hashes the prefix to the Oracle 19 "sun/util/resources/TimeZoneNamesBundle"

Regards

Jonathan Lewis

Jonathan Lewis

That structure looks like you're capturing the names of objects of type JAVA CLASS. Maybe they weren't being captured in 12c, or maybe the internal naming convention was different, but if you look in all_objects or dba_objects you'll see that objects of type JAVA CLASS can have names much longer than 30 characters - and the column is defined as varchar2(128).

For comparison I see in an 11g instance object_name: "/2ea59ec_TimeZoneNamesBundle" which probably hashes the prefix to the Oracle 19 "sun/util/resources/TimeZoneNamesBundle"

Regards

Jonathan Lewis

FBP_FLUOR

in the 19c db (select * from user_objects) i do see JAVA CLASS type. Not in the same query on a 12c db. also it appears that the insert in the trigger is using dbms_java.longname(object_name) instead of dbms_java.shortname(object_name). so that is where it is failing. Thank you.