A DDL Trigger ... and writing to the alert log file

 Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.

SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb1;

Session altered.

SQL> @Drop_Table_not_permitted.sql
SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED
  2  after ddl
  3  on database
  4  begin
  5    if (ora_sysevent='DROP'  and  ora_dict_obj_type = 'TABLE')
  6    then
  7   --- code for INSERT into an audit log table
  8   --- INSERT INTO ....
  9   ---
 10   --- code below is to write to the alert log file
 11   --- dbms_log is undocumented but available since 12c or 11.2.0.4
 12   ---   see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/
 13   --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt
 14   ---   see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html
 15       dbms_log.ksdwrt(2,' Warning :  ' || sys_context('USERENV','SESSION_USER')
 16                      || ' tried to drop  ' || ora_dict_obj_name
 17                      || '  in SID ' || sys_context('USERENV','SID')
 18                      || ', AUDSID ' || sys_context('USERENV','SESSIONID')
 19                      || '  and service name  ' || sys_context('USERENV','SERVICE_NAME'));
 20      raise_application_error(-20001,'You are not permitted to Drop Tables');
 21    end if;
 22  end;
 23  /

Trigger created.

SQL>

When I connect to the PDB and attempt to execute a DROP TABLE ...

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc dummy_tab
 Name                                                           Null?    Type
 -------------------------------------------------------------- -------- ------------------------
 ID_COL                                                                  NUMBER

SQL> drop table dummy_tab;
drop table dummy_tab
           *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not permitted to Drop Tables
ORA-06512: at line 17


SQL>

The alert log shows this entry :

2021-05-27T21:38:11.859746+08:00
ORCLPDB1(3): Warning :  HEMANT tried to drop  DUMMY_TAB  in SID 272, AUDSID 1061583  and service name  orclpdb1

You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.

Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file.  "3" would reference both files.