引言：前面有针对大表的管理维护有做过两次讨论与实现，第一版本相当于测试版本，在管理或技术上不够成熟完善，第二版针对第一版本
在导出备份、分区删除验证的部分增加了更多状态的判断，更能保证数据备份的准备度。由于前面两版实现逻辑都是先统一导出表分区数据
，最后才统一做分区维护操作，第一个是操作系统脚本，通过操作系统的定制任务定时运行，第二是数据库存储过程，通过数据库JOB定时运
行，两个排程在执行时间上不能保证第一个执行完了再执行第二个，如果第一步没有问题，则第二步统一实现倒是对于回滚在一致性上更有
保证；第三版则改变了前面两版的实现逻辑，整合两个排程为一个排程，通过在操作系统的定制任务来调用存储过程，采取导出一个，维护
一个，则保证了逻辑执行的时间先后顺序，如果有异常，则处理起来更有针对性。考虑到数据库安全管控的TRIGGER与 EXPDP 的冲突，增加
了部分安全方面的脚本，以下记录一下相关的实现过程，以备查验。

若有转载，请注明出处，若有考虑不周的地方，也敬请指教

1. 数据库安全管控TRIGGER

CREATE OR REPLACE TRIGGER SYSTEM.TRG_AUDIT_DB_DDL
   BEFORE DDL
   ON DATABASE
DECLARE
   user_var         VARCHAR2 (200);
   login_user_var   VARCHAR2 (200);
   ip_add_var       VARCHAR2 (200);
   terminal_var     VARCHAR2 (200);
   host_var         VARCHAR2 (200);
   session_id_var   NUMBER;
   sql_text         ora_name_list_t;
   i                INTEGER;
   state_sql        VARCHAR2 (4000);
BEGIN
   --获取DDL语句
   FOR i IN 1 .. ora_sql_txt (sql_text)
   LOOP
      state_sql := state_sql || sql_text (i);
   END LOOP;

--如果语句长度大于4000，则取前4000个字符
state_sql := SUBSTRB (state_sql, 1, 4000);

--调用oracle函数得到基本信息

   SELECT   SYS_CONTEXT ('USERENV', 'OS_USER'),
            SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
            SYS_CONTEXT ('USERENV', 'TERMINAL'),
            SYS_CONTEXT ('USERENV', 'HOST'),
            SYS_CONTEXT ('USERENV', 'SESSIONID')
     INTO   user_var,
            ip_add_var,
            terminal_var,
            host_var,
            session_id_var
     FROM   DUAL;

SELECT ora_login_user INTO login_user_var FROM DUAL;

   IF login_user_var = 'TOPUP'
   THEN
      raise_application_error (-20001, 'Restric DDL! Please contact DBA');
   END IF;

   --将得到的信息插入到审计表中(数据库自动维护部分不作记录)
   IF user_var != 'oracle'
   THEN
      INSERT INTO TOPUP.TAB_AUDIT_DDL_RECORD (time,
                                              session_id,
                                              os_user,
                                              ip_address,
                                              terminal,
                                              HOST,
                                              user_name,
                                              ddl_type,
                                              object_type,
                                              owner,
                                              object_name,
                                              sql_text)
        VALUES   (SYSDATE,
                  session_id_var,
                  user_var,
                  ip_add_var,
                  terminal_var,
                  host_var,
                  login_user_var,
                  ora_sysevent,
                  ora_dict_obj_type,
                  ora_dict_obj_owner,
                  ora_dict_obj_name,
                  state_sql);
   END IF;
END;
/

2. 安全管控日志记录表

CREATE TABLE TOPUP.TAB_AUDIT_DDL_RECORD
(
TIME         DATE,
SESSION_ID   NUMBER,
OS_USER      VARCHAR2(200 BYTE),
IP_ADDRESS   VARCHAR2(200 BYTE),
TERMINAL     VARCHAR2(200 BYTE),
HOST         VARCHAR2(200 BYTE),
USER_NAME    VARCHAR2(30 BYTE),
DDL_TYPE     VARCHAR2(30 BYTE),
OBJECT_TYPE VARCHAR2(18 BYTE),
OWNER        VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
SQL_TEXT     VARCHAR2(4000 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             20M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.TIME IS '操作时间';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.SESSION_ID IS '会话ID';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OS_USER IS '终端OS用户';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.IP_ADDRESS IS '终端IP地址';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.TERMINAL IS '终端';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.HOST IS '终端主机名';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.USER_NAME IS 'Oracle用户名';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.DDL_TYPE IS 'DDL操作类型';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OBJECT_TYPE IS '操作对象类型';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OWNER IS '对象所有者';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OBJECT_NAME IS '对象名称';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.SQL_TEXT IS 'DDL语句';

CREATE INDEX TOPUP.IDX_TAB_AUDIT_DDL_RECORD ON TOPUP.TAB_AUDIT_DDL_RECORD
(TIME)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE ON TOPUP.TAB_AUDIT_DDL_RECORD TO SYSTEM;

ITPub博客

第三版ORACLE 分区表定期自动分区导出及删除导出分区(一)

最新文章