oracle在现有表的基础上建立分区表

  1. 原表的sql语句如下:
    CREATE TABLE “CBE_MESSAGE”
    ( “ID_” VARCHAR2(72 CHAR) NOT NULL ENABLE,
    “CONTENT_” CLOB,
    “CREAE_TIME_” NUMBER(19,0),
    “KEY_” VARCHAR2(500 CHAR),
    “OPT_TIME_” NUMBER(19,0),
    “RETURN_INFO” VARCHAR2(500 CHAR),
    “RETURN_STATUS” VARCHAR2(200 CHAR),
    “RETURN_TIME_” TIMESTAMP (6),
    “SIZE_” NUMBER(19,0),
    “STATUS_” VARCHAR2(255 CHAR),
    “TITLE_” VARCHAR2(500 CHAR),
    “TYPE_” VARCHAR2(200 CHAR),
    “INSERT_TIME” TIMESTAMP (6) DEFAULT systimestamp,
    PRIMARY KEY (“ID_”)
    )

  2. 创建一张与原表字段一样的临时分区表:CBE_MESSAGE_TEMP,建表语句如下:
    CREATE TABLE “CBE_MESSAGE_TEMP”
    (“ID_” VARCHAR2(72 CHAR) NOT NULL ENABLE,
    “CONTENT_” CLOB,
    “CREAE_TIME_” NUMBER(19,0),
    “KEY_” VARCHAR2(500 CHAR),
    “OPT_TIME_” NUMBER(19,0),
    “RETURN_INFO” VARCHAR2(500 CHAR),
    “RETURN_STATUS” VARCHAR2(200 CHAR),
    “RETURN_TIME_” TIMESTAMP (6),
    “SIZE_” NUMBER(19,0),
    “STATUS_” VARCHAR2(255 CHAR),
    “TITLE_” VARCHAR2(500 CHAR),
    “TYPE_” VARCHAR2(200 CHAR),
    “INSERT_TIME” TIMESTAMP (6) DEFAULT systimestamp,
    PRIMARY KEY (“ID_”)
    )
    partition by range (INSERT_TIME) interval (NUMTODSINTERVAL (5, ‘day’) )
    (partition default_ptablen values less than (to_date(‘2017-02-13’, ‘yyyy-mm-dd’)));

  3. 将原表数据插入新建的临时分区表CBE_MESSAGE_TEMP,插入语句如下:
    INSERT INTO CBE_MESSAGE_TEMP (ID_,CONTENT_,CREAE_TIME_,KEY_,OPT_TIME_,RETURN_INFO,RETURN_STATUS,RETURN_TIME_,SIZE_,STATUS_,TITLE_,TYPE_,INSERT_TIME)
    SELECT ID_,CONTENT_,CREAE_TIME_,KEY_,OPT_TIME_,RETURN_INFO,RETURN_STATUS,RETURN_TIME_,SIZE_,STATUS_,TITLE_,TYPE_,INSERT_TIME FROM CBE_MESSAGE;

  4. 将原表重命名为CBE_MESSAGE_OLD,sql语句如下:
    RENAME CBE_MESSAGE TO CBE_MESSAGE_OLD;

  5. 将新建的临时分区表CBE_MESSAGE_TEMP重命名为CBE_MESSAGE,sql语句如下:
    RENAME CBE_MESSAGE_TEMP TO CBE_MESSAGE;

  6. 删除表CBE_MESSAGE_OLD,sql如下
    DROP TABLE CBE_MESSAGE_OLD;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值