--1、CREATE TABLESPACE、USER AND GRANT ROLE TO USER-----------
DROP TABLESPACE YGZHOU INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE YGZHOU DATAFILE '/data1/stream/ygzhou01/datafile/ygzhou01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5m;
CREATE USER YGZHOU IDENTIFIED BY YGZHOU DEFAULT TABLESPACE YGZHOU;
GRANT DBA TO YGZHOU;
ALTER USER YGZHOU DEFAULT ROLE ALL;
GRANT CREATE SESSION TO YGZHOU;
GRANT UNLIMITED TABLESPACE TO YGZHOU;
------------------------------------------------------------
--GRANT SELECT ON SYS.V_$SQL TO YGZHOU;
--GRANT SELECT ON SYS.V_$SQL_BIND_DATA TO YGZHOU;
--GRANT SELECT ON SYS.V_$SQL_CURSOR TO YGZHOU;
--GRANT SELECT ON SYS.V_$SESSION TO YGZHOU;
--GRANT CREATE TRIGGER TO YGZHOU;
--2、CREATE TABLE ------------------------------------------
DROP TABLE YGZHOU.DDL_LOG CASCADE CONSTRAINTS;

CREATE TABLE YGZHOU.DDL_LOG
(
DB_NAME      VARCHAR2(20 BYTE),
USERNAME     VARCHAR2(20 BYTE),
CLIENT_IP    VARCHAR2(20 BYTE),
DDL_SCRIPTS VARCHAR2(4000 BYTE),
RECORD_TIME DATE DEFAULT SYSDATE
);
--3、CREATE TRIGGER----------------------------------------
DROP TRIGGER YGZHOU.DDL_LOG;
CREATE OR REPLACE TRIGGER YGZHOU.DDL_LOG AFTER DDL ON DATABASE
DECLARE
SQL_TEXT            ORA_NAME_LIST_T;
SQL_STMT            VARCHAR2(4000);
-- v_DB_ID       VARCHAR2(20);

--CREATED BY Ygzhou518
--Date:2012-03-13

BEGIN
SQL_STMT:='DB ';
-- v_DB_ID:='YGZHOUDB'; --The value must be registered in table DBMS.INSTANCE_INFO.

FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP
IF LENGTH(SQL_STMT)<3800 THEN
SQL_STMT := SQL_STMT||SQL_TEXT(I);
END IF;
END LOOP;

INSERT INTO YGZHOU.DDL_LOG (DB_NAME,USERNAME,CLIENT_IP,RECORD_TIME,DDL_SCRIPTS)
SELECT
--v_DB_ID,
'YGZHOUDB',ORA_LOGIN_USER,SYS_CONTEXT('USERENV','IP_ADDRESS'),SYSDATE, SQL_STMT FROM DUAL;
END ;
/
-------------------------------------------------------------------------------------------------

Create a trigger TO monitoring DDL