创建分区表

1 创建分区表，分区字段原来为timestamp数据类型的

create table tmp
(
  DEALER_ID           NUMBER(8) not null,
  SO_NO               CHAR(12) not null,
  SO_STATUS           NUMBER(8) not null,
  COMPANY_ID          NUMBER(8) not null,
  DEALER_CODE         VARCHAR2(16) not null,
  CUSTOMER_ID         NUMBER(12) not null,
  VEHICLE_ID          NUMBER(12) not null,
  VIN                 VARCHAR2(17) not null,
  SERVICE_TYPE        NUMBER(8) not null,
  FREE_AS_TYPE        NUMBER(8),
  INSURER_NAME        VARCHAR2(128 CHAR),
  ADVISOR_NAME        VARCHAR2(32 CHAR),
  ARTIFICER_NAME      VARCHAR2(32 CHAR),
  COMING_TIME         TIMESTAMP(6),
  FINISHING_TIME      TIMESTAMP(6),
  CI_CHECKED          NUMBER(1) default 0,
  PLATE_NO            VARCHAR2(32 CHAR),
  BRAND_CODE          VARCHAR2(32),
  SERIES_CODE         VARCHAR2(32),
  MODEL_CODE          VARCHAR2(32),
  COLOR_CODE          VARCHAR2(32),
  VEHICLE_USES        NUMBER(8),
  ENGINE_NO           VARCHAR2(32),
  GEARBOX_NO          VARCHAR2(64),
  WARRANTY_START_DATE TIMESTAMP(6),
  PRODUCTION_DATE     TIMESTAMP(6),
  SOLD_DATE           TIMESTAMP(6),
  NO_WARRANTY         NUMBER(1) default 0,
  NEW_ODOMETER        NUMBER(1) default 0,
  ODO_MILEAGE         NUMBER(10,2),
  MILEAGE_SUM         NUMBER(10,2),
  VC_REFRESHED        NUMBER(1) default 0,
  OWNER_NAME          VARCHAR2(128 CHAR),
  OWNER_GENDER        NUMBER(8),
  OWNER_MOBILE        VARCHAR2(32),
  OWNER_PHONE         VARCHAR2(32),
  COUNTRY             NUMBER(8),
  PROVINCE            NUMBER(8),
  CITY                NUMBER(8),
  COUNTY              NUMBER(8),
  OWNER_ADDRESS       VARCHAR2(128 CHAR),
  ZIP_CODE            VARCHAR2(8),
  REPAIR_MAN          VARCHAR2(32 CHAR),
  REPAIR_MAN_GANDER   NUMBER(8),
  REPAIR_MAN_MOBILE   VARCHAR2(32),
  REPAIR_MAN_PHONE    VARCHAR2(32),
  REMARK_128          VARCHAR2(128 CHAR),
  SO_SUM_AMT          NUMBER(10,2),
  DISCOUNT_MODE_N     VARCHAR2(64 CHAR),
  DISCOUNT_RATE       NUMBER(8,4),
  DISCOUNT_AMT        NUMBER(10,2),
  ODD_AMT             NUMBER(10,2),
  SO_RC_AMT           NUMBER(10,2),
  SO_MH_AMT           NUMBER(10,2),
  SO_SP_AMT           NUMBER(10,2),
  SO_AD_AMT           NUMBER(10,2),
  SO_AX_AMT           NUMBER(10,2),
  SO_MG_AMT           NUMBER(10,2),
  PAY_MODE            NUMBER(8),
  COMPLETED_DATE      TIMESTAMP(6),
  COMPLETED_BY_N      VARCHAR2(32 CHAR),
  SETTLED_DATE        TIMESTAMP(6),
  SO_ST_NO            CHAR(12),
  SETTLED_BY_N        VARCHAR2(32 CHAR),
  APPRAISED_DATE      TIMESTAMP(6),
  APPRAISED_BY_N      VARCHAR2(32 CHAR),
  BEST_INQ_DATE       TIMESTAMP(6),
  NEXT_DATE_MT        TIMESTAMP(6),
  NEXT_MILEAGE_MT     NUMBER(10,2),
  SO_INQUIRING        NUMBER(1) default 0,
  NO_INQ_CAUSE        VARCHAR2(64 CHAR),
  DELAYED_CAUSE       VARCHAR2(128 CHAR),
  INSPECTED_DESC      VARCHAR2(128 CHAR),
  CUST_OPINION        VARCHAR2(128 CHAR),
  COMPLETED_DESC      VARCHAR2(128 CHAR),
  AT_STATION          NUMBER(1) default 1,
  OIL_SCALE           NUMBER(8),
  TAKING_SCRAP        NUMBER(1) default 0,
  SO_FAULT_DESC       VARCHAR2(128 CHAR),
  SO_DIAGNOSES        VARCHAR2(128 CHAR),
  SO_FAULT_CAUSE      VARCHAR2(128 CHAR),
  SO_CHECK_DESC       VARCHAR2(128 CHAR),
  UPLOAN_TIMES        NUMBER(8) default 0,
  CREATED_BY          NUMBER(8),
  CREATED_TIME        TIMESTAMP(6),
  UPDATED_BY          NUMBER(8),
  UPDATED_TIME        TIMESTAMP(6),
  VERSION             NUMBER(8) default 0,
  SS_COMPANY_ID       NUMBER(8) default -1
)
 PARTITION BY RANGE (COMING_TIME)
 ( PARTITION PART_2011 VALUES LESS THAN   (to_date('2012-01-01','yyyy-mm-dd')),
   PARTITION PART_2012_1 VALUES LESS THAN (to_date('2012-04-01','yyyy-mm-dd')),
   PARTITION PART_2012_2 VALUES LESS THAN (to_date('2012-07-01','yyyy-mm-dd')),
   PARTITION PART_2012_3 VALUES LESS THAN (to_date('2012-10-01','yyyy-mm-dd')),
   PARTITION PART_2012_4 VALUES LESS THAN (to_date('2013-01-01','yyyy-mm-dd')),
   PARTITION PART_2013_1 VALUES LESS THAN (to_date('2013-04-01','yyyy-mm-dd')),
   PARTITION PART_2013_2 VALUES LESS THAN (to_date('2013-07-01','yyyy-mm-dd')),
   PARTITION PART_2013_3 VALUES LESS THAN (to_date('2013-10-01','yyyy-mm-dd')),
   PARTITION PART_2013_4 VALUES LESS THAN (to_date('2014-01-01','yyyy-mm-dd')),
   PARTITION PART_2014_1 VALUES LESS THAN (to_date('2014-04-01','yyyy-mm-dd')),
   PARTITION PART_2014_2 VALUES LESS THAN (to_date('2014-07-01','yyyy-mm-dd')),
   PARTITION PART_2014_3 VALUES LESS THAN (to_date('2014-10-01','yyyy-mm-dd')),
   PARTITION PART_2014_4 VALUES LESS THAN (to_date('2015-01-01','yyyy-mm-dd'))
    )
 
 
 2 创建后查看分区

create table TMP
(
  DEALER_ID           NUMBER(8) not null,
  SO_NO               CHAR(12) not null,
  SO_STATUS           NUMBER(8) not null,
  COMPANY_ID          NUMBER(8) not null,
  DEALER_CODE         VARCHAR2(16) not null,
  CUSTOMER_ID         NUMBER(12) not null,
  VEHICLE_ID          NUMBER(12) not null,
  VIN                 VARCHAR2(17) not null,
  SERVICE_TYPE        NUMBER(8) not null,
  FREE_AS_TYPE        NUMBER(8),
  INSURER_NAME        VARCHAR2(128 CHAR),
  ADVISOR_NAME        VARCHAR2(32 CHAR),
  ARTIFICER_NAME      VARCHAR2(32 CHAR),
  COMING_TIME         TIMESTAMP(6),
  FINISHING_TIME      TIMESTAMP(6),
  CI_CHECKED          NUMBER(1) default 0,
  PLATE_NO            VARCHAR2(32 CHAR),
  BRAND_CODE          VARCHAR2(32),
  SERIES_CODE         VARCHAR2(32),
  MODEL_CODE          VARCHAR2(32),
  COLOR_CODE          VARCHAR2(32),
  VEHICLE_USES        NUMBER(8),
  ENGINE_NO           VARCHAR2(32),
  GEARBOX_NO          VARCHAR2(64),
  WARRANTY_START_DATE TIMESTAMP(6),
  PRODUCTION_DATE     TIMESTAMP(6),
  SOLD_DATE           TIMESTAMP(6),
  NO_WARRANTY         NUMBER(1) default 0,
  NEW_ODOMETER        NUMBER(1) default 0,
  ODO_MILEAGE         NUMBER(10,2),
  MILEAGE_SUM         NUMBER(10,2),
  VC_REFRESHED        NUMBER(1) default 0,
  OWNER_NAME          VARCHAR2(128 CHAR),
  OWNER_GENDER        NUMBER(8),
  OWNER_MOBILE        VARCHAR2(32),
  OWNER_PHONE         VARCHAR2(32),
  COUNTRY             NUMBER(8),
  PROVINCE            NUMBER(8),
  CITY                NUMBER(8),
  COUNTY              NUMBER(8),
  OWNER_ADDRESS       VARCHAR2(128 CHAR),
  ZIP_CODE            VARCHAR2(8),
  REPAIR_MAN          VARCHAR2(32 CHAR),
  REPAIR_MAN_GANDER   NUMBER(8),
  REPAIR_MAN_MOBILE   VARCHAR2(32),
  REPAIR_MAN_PHONE    VARCHAR2(32),
  REMARK_128          VARCHAR2(128 CHAR),
  SO_SUM_AMT          NUMBER(10,2),
  DISCOUNT_MODE_N     VARCHAR2(64 CHAR),
  DISCOUNT_RATE       NUMBER(8,4),
  DISCOUNT_AMT        NUMBER(10,2),
  ODD_AMT             NUMBER(10,2),
  SO_RC_AMT           NUMBER(10,2),
  SO_MH_AMT           NUMBER(10,2),
  SO_SP_AMT           NUMBER(10,2),
  SO_AD_AMT           NUMBER(10,2),
  SO_AX_AMT           NUMBER(10,2),
  SO_MG_AMT           NUMBER(10,2),
  PAY_MODE            NUMBER(8),
  COMPLETED_DATE      TIMESTAMP(6),
  COMPLETED_BY_N      VARCHAR2(32 CHAR),
  SETTLED_DATE        TIMESTAMP(6),
  SO_ST_NO            CHAR(12),
  SETTLED_BY_N        VARCHAR2(32 CHAR),
  APPRAISED_DATE      TIMESTAMP(6),
  APPRAISED_BY_N      VARCHAR2(32 CHAR),
  BEST_INQ_DATE       TIMESTAMP(6),
  NEXT_DATE_MT        TIMESTAMP(6),
  NEXT_MILEAGE_MT     NUMBER(10,2),
  SO_INQUIRING        NUMBER(1) default 0,
  NO_INQ_CAUSE        VARCHAR2(64 CHAR),
  DELAYED_CAUSE       VARCHAR2(128 CHAR),
  INSPECTED_DESC      VARCHAR2(128 CHAR),
  CUST_OPINION        VARCHAR2(128 CHAR),
  COMPLETED_DESC      VARCHAR2(128 CHAR),
  AT_STATION          NUMBER(1) default 1,
  OIL_SCALE           NUMBER(8),
  TAKING_SCRAP        NUMBER(1) default 0,
  SO_FAULT_DESC       VARCHAR2(128 CHAR),
  SO_DIAGNOSES        VARCHAR2(128 CHAR),
  SO_FAULT_CAUSE      VARCHAR2(128 CHAR),
  SO_CHECK_DESC       VARCHAR2(128 CHAR),
  UPLOAN_TIMES        NUMBER(8) default 0,
  CREATED_BY          NUMBER(8),
  CREATED_TIME        TIMESTAMP(6),
  UPDATED_BY          NUMBER(8),
  UPDATED_TIME        TIMESTAMP(6),
  VERSION             NUMBER(8) default 0,
  SS_COMPANY_ID       NUMBER(8) default -1
)
partition by range (COMING_TIME)
(
  partition PART_2011 values less than (TIMESTAMP' 2012-01-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2012_1 values less than (TIMESTAMP' 2012-04-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2012_2 values less than (TIMESTAMP' 2012-07-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2012_3 values less than (TIMESTAMP' 2012-10-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2012_4 values less than (TIMESTAMP' 2013-01-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2013_1 values less than (TIMESTAMP' 2013-04-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2013_2 values less than (TIMESTAMP' 2013-07-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2013_3 values less than (TIMESTAMP' 2013-10-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2013_4 values less than (TIMESTAMP' 2014-01-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2014_1 values less than (TIMESTAMP' 2014-04-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2014_2 values less than (TIMESTAMP' 2014-07-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2014_3 values less than (TIMESTAMP' 2014-10-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PART_2014_4 values less than (TIMESTAMP' 2015-01-01 00:00:00')
    tablespace DCS01
    pctfree 10
    initrans 1
    maxtrans 255
);

 

 

3.怎么回事

 

自动转化