select * from dba_triggers;

select distinct triggering_event from dba_triggers;
select distinct '[' || triggering_event || ']' xxx from dba_triggers;
select * from dba_triggers where triggering_event = 'LOGON ';

select * from dba_triggers where trigger_name like 'XXX';

select * from dba_triggers where trigger_name like 'USER_TRACE_TRG';

select distinct trigger_type from dba_triggers;

select distinct triggering_event from dba_triggers;

select * from dba_triggers where trigger_name like 'MY_TRIGGER_NAME';

select * from dba_triggers where triggering_event = 'LOGON';

select * from dba_triggers where trigger_name like '%';

select distinct '[' || triggering_event || ']' xxx from dba_triggers;

select * from dba_triggers where triggering_event = 'LOGON ';

select * from dba_triggers where triggering_event = 'STARTUP ';

select * from dba_triggers where triggering_event = 'SHUTDOWN ';

rem verifying dropped:
set feedback on pagesize 500 linesize 180 trimspool on
column object_name format a32
select owner, object_name, object_type from dba_objects where  owner=upper('&my_username');

select * from dba_triggers where triggering_event = 'LOGON ';

-- logon trigger on schema
CREATE OR REPLACE TRIGGER MYSCHEMA.MYSCHEMA_LOGON_TRIGGER_#MY_TRIGGER
  AFTER LOGON
  ON MYSCHEMA.SCHEMA
BEGIN
  execute immediate  'alter session set cursor_sharing=''FORCE''';
END;
/

-- ---------------------------------------

-- logon trigger on database, usually created in sys schema
CREATE OR REPLACE TRIGGER SYS.MY_DATABASE_LEVEL_LOGON_TRIGGER_#MY_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'MYUSERNAME1'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

-- startup trigger on database or after startup on database 
-- select * from dba_triggers where triggering_event = 'STARTUP ';

CREATE OR REPLACE TRIGGER MYSCHEMA.MY_STARTUP AFTER STARTUP ON DATABASE
BEGIN
  MY_PACKAGE.MY_STORED_PROCEDURE;
EXCEPTION
  -- Trap all exceptions so database startup will not be impacted
  -- by errors.  Called routines should handle errors appropriately.
  WHEN OTHERS THEN
    NULL;
END;
/

CREATE OR REPLACE TRIGGER MYSCHEMA.ON_STARTUP
   after startup
   on database
begin
      delete from my_temp_schema;
   end;
/

CREATE OR REPLACE TRIGGER DBAS.dba_after_db_startup_trig
 AFTER STARTUP ON DATABASE
DECLARE
    v_skip          CHAR(1) ;
BEGIN
      v_skip          := 'N' ;
      v_open_mode_txt := ' ' ;

      BEGIN
           SELECT   LOG_MODE
                  , OPEN_MODE
                  , NAME
           INTO     v_log_mode_txt
                  , v_open_mode_txt
                  , v_db_nam
           FROM sys.v_$database;
           IF v_open_mode_txt = 'READ ONLY'  THEN
              v_skip := 'Y' ;
           END IF;
        EXCEPTION
         WHEN NO_DATA_FOUND THEN
               v_skip := 'Y' ;
      END ;
END;
/

-- before shutdown on database
-- select * from dba_triggers where triggering_event = 'SHUTDOWN ';

CREATE OR REPLACE TRIGGER MYSCHEMA.ON_SHUTDOWN
   before shutdown
   on database
begin
      delete from my_temp_table;
end;
/

CREATE OR REPLACE TRIGGER MYSCHEMA.before_db_shutdown_trigger
 BEFORE SHUTDOWN ON DATABASE
DECLARE
    v_skip          CHAR(1) ;
BEGIN
      v_skip          := 'N' ;
      v_open_mode_txt := ' ' ;

      BEGIN
           SELECT   LOG_MODE
                  , OPEN_MODE
                  , NAME
           INTO     v_log_mode_txt
                  , v_open_mode_txt
                  , v_db_nam
           FROM sys.v_$database;
           IF v_open_mode_txt = 'READ ONLY'  THEN
              v_skip := 'Y' ;
           END IF;
        EXCEPTION
         WHEN NO_DATA_FOUND THEN
               v_skip := 'Y' ;
      END ;
END;
/

select * from dba_triggers where trigger_name = 'DDL_TRIGGER';

select * from dba_triggers where trigger_event='CREATE OR ALTER OR DROP OR GRANT OR REVOKE OR TRUNCATE OR RENAME ';

select * from dba_triggers where  trigger_event like 'CREATE%'
                               or trigger_event like 'ALTER%'
                               or trigger_event like 'DROP%'
                               or trigger_event like 'GRANT%'
                               or trigger_event like 'REVOKE%'
                               or trigger_event like 'TRUNCATE%'
                               or trigger_event like 'RENAME%';

-- DDL TRIGGER
CREATE OR REPLACE TRIGGER DBAS.DDL_TRIGGER
 before create or alter or drop or rename or truncate or revoke or grant
  on SCHEMA
declare
      l_sysevent varchar2(25);
      l_sid integer;
  begin
      select ora_sysevent into l_sysevent from dual;
          l_sid:=to_number(substr(dbms_session.unique_session_id,1,4),'XXXX');

      if ( l_sysevent in ('DROP','CREATE', 'TRUNCATE') )
      then
          insert into MYSCHEMA.AUDIT_DDL
          select sysdate, ora_sysevent, ora_dict_obj_owner,
                 ora_dict_obj_name, null,  s.machine, ora_dict_obj_type,
                 s.osuser
            from v$session s
            where s.sid=l_sid and ora_dict_obj_type <> 'USER';
      elsif ( l_sysevent = 'ALTER' )
      then
        if ( ora_dict_obj_type <> 'USER')
        then
            insert into MYSCHEMA.AUDIT_DDL
            (DDL_DATE, OPERATION, OWNER, NAME, EXTRA, MACHINE, OBJ_TYPE, OSUSER)
            select sysdate, ora_sysevent, ora_dict_obj_owner,
                           ora_dict_obj_name, sql_text, s.machine,
                           ora_dict_obj_type, s.osuser
              from v$open_cursor c, v$session s
              where upper(sql_text) like 'ALTER%'
                    and c.sid=s.sid and s.sid=l_sid;
        end if;
      elsif ( l_sysevent = 'RENAME' )
      then
          insert into MYSCHEMA.AUDIT_DDL
          select sysdate, ora_sysevent, ora_dict_obj_owner,
                         ora_dict_obj_name, sql_text, s.machine,
                         ora_dict_obj_type, s.osuser
            from v$open_cursor c, v$session s
            where upper(sql_text) like 'RENAME%'
                  and c.sid=s.sid and s.sid=l_sid;
      elsif ( l_sysevent = 'REVOKE' )
      then
          insert into MYSCHEMA.AUDIT_DDL
          select sysdate, ora_sysevent, ora_dict_obj_owner,
                         ora_dict_obj_name, sql_text, s.machine,
                         ora_dict_obj_type, s.osuser
            from v$open_cursor c, v$session s
            where upper(sql_text) like 'REVOKE%'
                  and c.sid=s.sid and s.sid=l_sid;
      end if;
  end;
/