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

1. 大表分区结构

CREATE TABLE TAB_BASE_OPERATIONLOG_BAK
(
  ID              NUMBER(19)                    NOT NULL,
  CREATEDATE      TIMESTAMP(6),
  DESCRIPTION     VARCHAR2(200 CHAR),
  LASTDATE        TIMESTAMP(6),
  OPERATIONCLASS  VARCHAR2(100 CHAR),
  PK              NUMBER(19),
  SESSIONID       VARCHAR2(100 CHAR),
  ACCOUNT_ID      NUMBER(19),
  OPERATION_ID    NUMBER(19)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    20
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (CREATEDATE)
( 
  PARTITION P20120714 VALUES LESS THAN (TIMESTAMP' 2012-07-15 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120715 VALUES LESS THAN (TIMESTAMP' 2012-07-16 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120716 VALUES LESS THAN (TIMESTAMP' 2012-07-17 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120717 VALUES LESS THAN (TIMESTAMP' 2012-07-18 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120718 VALUES LESS THAN (TIMESTAMP' 2012-07-19 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120719 VALUES LESS THAN (TIMESTAMP' 2012-07-20 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE TAB_TOPUP_CARDTOPLOG
(
  ID           NUMBER(19)                       NOT NULL,
  ORDERNUMBER  VARCHAR2(100 CHAR),
  LOGDESCRIP   VARCHAR2(4000 CHAR),
  CREATEDATE   TIMESTAMP(6)                     NOT NULL,
  LASTDATE     TIMESTAMP(6),
  METHOD       CHAR(100 BYTE)                   NOT NULL
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (CREATEDATE)
( 
  PARTITION P20120713 VALUES LESS THAN (TIMESTAMP' 2012-07-14 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120714 VALUES LESS THAN (TIMESTAMP' 2012-07-15 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120715 VALUES LESS THAN (TIMESTAMP' 2012-07-16 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120716 VALUES LESS THAN (TIMESTAMP' 2012-07-17 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120717 VALUES LESS THAN (TIMESTAMP' 2012-07-18 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120718 VALUES LESS THAN (TIMESTAMP' 2012-07-19 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    20
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P20120719 VALUES LESS THAN (TIMESTAMP' 2012-07-20 00:00:00')
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-736027/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-736027/