Re: DDL trigger only working on one database

On Tue, 29 Oct 2002 14:35:49 -0800, Eric Kimble <ekimble_at_wfl.fha.dot.gov> wrote:

>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;
>/
>

Please remove the exception section, so that you can at least post an error message

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Oct 29 2002 - 23:43:11 CST