use event attributes to provide more info : Schema Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Schema Trigger

SQL>
SQL> CREATE OR REPLACE TRIGGER town_crier
  2  AFTER CREATE ON SCHEMA
  3  BEGIN
  4
  5    DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||
  6                         ORA_DICT_OBJ_TYPE || ' called ' ||
  7                         ORA_DICT_OBJ_NAME);
  8  END;
  9  /

Trigger created.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE a_table
  2  (col1 NUMBER);
I believe you have created a TABLE called A_TABLE

Table created.

SQL>
SQL>
SQL> CREATE INDEX an_index ON a_table(col1);
I believe you have created a INDEX called AN_INDEX

Index created.

SQL>
SQL> drop index an_index;

Index dropped.

SQL>
SQL>
SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS
  2  BEGIN
  3    RETURN(TRUE);
  4  END;
  5  /
I believe you have created a FUNCTION called A_FUNCTION

Function created.

SQL>
SQL> DROP FUNCTION a_function;

Function dropped.

SQL>
SQL> EXEC DBMS_OUTPUT.PUT_LINE(CHR(10));



PL/SQL procedure successfully completed.

SQL>
SQL> drop trigger town_crier;

Trigger dropped.

SQL>
SQL> DROP TABLE a_table;

Table dropped.