以前也写过一个DDL记录的触发器，今天在METALINK上看到个更好的，抄袭下

CREATE TABLE DDL_ACTIONS
(
counter number(38)
,user_name VARCHAR2(4000)
,ddl_date VARCHAR2(26)
,ddl_type VARCHAR2(4000)
,object_type VARCHAR2(4000)
,owner VARCHAR2(4000)
,object_name VARCHAR2(4000)
,sqltext CLOB
);

CREATE OR REPLACE TRIGGER DDLTRIGGER
 AFTER DDL ON DATABASE
DECLARE
 L_CNT      BINARY_INTEGER := 0;
 L_LEN      INTEGER := 0;
 L_NO       INTEGER := 1;
 L_S        VARCHAR2(32767) := '';
 L_SQL_TEXT ORA_NAME_LIST_T;
BEGIN
 L_CNT := ORA_SQL_TXT(L_SQL_TEXT);
 FOR I IN 1 .. L_CNT LOOP
  IF L_CNT = 1 THEN
   INSERT INTO DDL_ACTIONS
    (COUNTER,
     USER_NAME,
     DDL_DATE,
     DDL_TYPE,
     OBJECT_TYPE,
     OWNER,
     OBJECT_NAME,
     SQLTEXT)
   VALUES
    (I,
     ORA_LOGIN_USER,
     TO_CHAR(SYSTIMESTAMP, 'yyyy.mm.dd hh24:mi:ss.FF'),
     ORA_SYSEVENT,
     ORA_DICT_OBJ_TYPE,
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_NAME,
     L_SQL_TEXT(I));
  ELSE
   IF L_LEN + LENGTH(L_SQL_TEXT(I)) > 32767 THEN
    -- insert
    INSERT INTO DDL_ACTIONS
     (COUNTER,
      USER_NAME,
      DDL_DATE,
      DDL_TYPE,
      OBJECT_TYPE,
      OWNER,
      OBJECT_NAME,
      SQLTEXT)
    VALUES
     (L_NO,
      ORA_LOGIN_USER,
      TO_CHAR(SYSTIMESTAMP, 'yyyy.mm.dd hh24:mi:ss.FF'),
      ORA_SYSEVENT,
      ORA_DICT_OBJ_TYPE,
      ORA_DICT_OBJ_OWNER,
      ORA_DICT_OBJ_NAME,
      L_S);
    L_LEN := LENGTH(L_SQL_TEXT(I));
    L_S   := L_SQL_TEXT(I);
    L_NO  := L_NO + 1;
   ELSE
    -- concat
    L_S   := L_S || L_SQL_TEXT(I);
    L_LEN := L_LEN + LENGTH(L_SQL_TEXT(I));
   END IF;
  END IF;
 END LOOP;
 IF L_S != '' THEN
  INSERT INTO DDL_ACTIONS
   (COUNTER,
    USER_NAME,
    DDL_DATE,
    DDL_TYPE,
    OBJECT_TYPE,
    OWNER,
    OBJECT_NAME,
    SQLTEXT)
  VALUES
   (L_NO,
    ORA_LOGIN_USER,
    TO_CHAR(SYSTIMESTAMP, 'yyyy.mm.dd hh24:mi:ss.FF'),
    ORA_SYSEVENT,
    ORA_DICT_OBJ_TYPE,
    ORA_DICT_OBJ_OWNER,
    ORA_DICT_OBJ_NAME,
    L_S);
 END IF;
END;
/