- 3,714,556 Users
- 2,242,576 Discussions
- 7,844,931 Comments
Forum Stats
Discussions
Categories
- 12 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 440 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.7K ORDS, SODA & JSON in the Database
- 415 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135K Development Tools
- 7 DevOps
- 3K QA/Testing
- 231 Java
- 4 Java Learning Subscription
- 10 Database Connectivity
- 65 Java Community Process
- Java 25
- 8 Java APIs
- 141.1K Java Development Tools
- 5 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 134 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 118 LiveLabs
- 28 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
AUDIT DDL trigger / table causing issues

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!
Best Answer
-
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
Answers
-
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
-
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.