Create a trigger TO monitoring DDL

ygzhou518發表於2012-03-13
--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 ;
/
-------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-718434/，如需轉載，請註明出處，否則將追究法律責任。

相關文章