Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

AUDIT DDL trigger / table causing issues

FBP_FLUORNov 23 2020 — edited Nov 23 2020

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!

This post has been answered by Jonathan Lewis on Nov 28 2020
Jump to Answer

Comments

Processing

Post Details

Added on Nov 23 2020
2 comments
1,181 views