I've got a kmsrp schema. I log into the db as kmsrp and I've created several variations of the following trigger (the current one is shown below):
SQL> create or replace trigger set_trace after logon on database
2 begin
3 if user not in ('SYS','SYSTEM') then
4 -- execute immediate 'alter session set timed_statistics=true';
5 -- execute immediate 'alter session set max_dump_file_size=unlimited';
6 -- execute immediate 'alter session set sql_trace=true';
7 execute immediate 'alter session set tracefile_identifier="KMSRP"';
8 execute immediate 'ALTER SESSION SET EVENTS "10046 trace name context forever, level 12"';
9 end if;
10 exception
11 when others then
12 null;
13 end;
14 /
Trigger created.
SQL> ALTER TRIGGER "KMSRP"."SET_TRACE" ENABLE;
Trigger altered.
I then log out and log back in. I run the following short script to create a table and delete some records. That should've been logged, but where?
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
------------------------------------------------------------------------------------------------------------------------
D:\APP\diag\rdbms\kms12wecs\kms12wecs\trace\kms12wecs_ora_2544_KMSRP.trc
SQL> create table rudytest1
2 as select username
3 from dba_users;
Table created.
SQL>
SQL> delete from rudytest1 where username in ('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG');
9 rows deleted.
SQL> drop table rudytst;
drop table rudytst
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table rudytest1;
Table dropped.
SQL> exit
Message was edited by: 3028740
Just a bit more info. I log in as dba and run the following (with the results):
select owner, trigger_name, trigger_type, triggering_event, table_owner, base_object_type, status from dba_triggers where trigger_name='SET_TRACE';
KMSRP, SET_TRACE, AFTER EVENT, LOGON, SYS, DATABASE, ENABLED
select owner, object_name, object_type, status from dba_objects where object_name='SET_TRACE';
KMSRP, SET_TRACE, TRIGGER, VALID