DDL trigger only working on one database

From: Eric Kimble <ekimble_at_wfl.fha.dot.gov>
Date: Tue, 29 Oct 2002 14:35:49 -0800
Message-ID: <3DBF0D45.C420A7E3@wfl.fha.dot.gov>

Hello, I was trying to implement the ddl trigger exercise located on Jonathan's page written by Norman (thanks for all you folks have done, I really appreciate it) on one instance. It didn't work (no errors so I tried it on another and it worked. I don't get any error just no data. EKIMBLE_at_DOLPHIN>alter table test nologging;

Table altered.

EKIMBLE_at_DOLPHIN>alter table test logging;

Table altered.

EKIMBLE_at_DOLPHIN>select * from logging.ddl_log;

no rows selected

between the 2 machines I used the same script and only changed the location of the datafile for the tablespace. They are both 8.1.7.4 running on nt and 2k. The one on NT works the one on 2000 didn't. I took out the exceptions clause and tried it that way to see if it was created errors and it didn't change anything. Here is a copy of the script that I used.

create tablespace logging
datafile 'c:\oracle\oradata\dolphin\logging01.dbf' size 200M extent management local
uniform size 64K;

create user logging identified by logging default tablespace logging
temporary tablespace temp
quota 0 on SYSTEM
quota 200M on LOGGING;

create table logging.ddl_log

( user_name        varchar2(30),
ddl_date         date,
ddl_type         varchar2(30),
object_type      varchar2(18),
owner            varchar2(30),
object_name      varchar2(128)

) tablespace logging;

--Create the trigger -
create or replace trigger DDLTrigger
after DDL on database
begin
  insert into logging.ddl_log
    values (ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type,

            ora_dict_obj_owner, ora_dict_obj_name); exception
when others then
NULL;
end;
/ Received on Tue Oct 29 2002 - 16:35:49 CST