带clob的单表改成分区表

SQL> col ITF_NAME for a40
SQL> select id,ITF_NAME,CREATE_DATE from dbmt.RESOURCES_S_MSG;

    ID ITF_NAME                                 CREATE_DATE

659782317 ccc 2020-01-22 01:17:44
668624356 ccc 2020-01-23 01:21:01
1306410754 ccc 2020-04-17 01:26:08
1318434865 ccc 2020-05-05 01:27:08
。。。。。。。。。。。。
1334978163 ccc 2020-06-14 01:22:41
1335211251 ccc 2020-06-15 01:22:44
1335865612 ccc 2020-06-16 01:22:47

这个表的ddl dbmt.RESOURCES_S_MSG

CREATE TABLE “DBMT”.“RESOURCES_S_MSG”
( “ID” NUMBER(19,0) NOT NULL ENABLE,
“ITF_NAME” VARCHAR2(240),
“ITF_CLASS” VARCHAR2(240),
“SEND_MSG” CLOB,
“RECEIVE_MSG” CLOB,
“SERVICE_NAME” VARCHAR2(10),
“EXCEPTION_MSG” VARCHAR2(4000),
“CREATE_DATE” DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “DATA”
LOB (“SEND_MSG”) STORE AS SECUREFILE (
TABLESPACE “DATA123” ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB (“RECEIVE_MSG”) STORE AS SECUREFILE (
TABLESPACE “DATA123” ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

SQL>

------------新创建的分区表

create table DBMT.RESOURCES_S_MSG_new
(
“ID” NUMBER(19,0) NOT NULL ENABLE,
“ITF_NAME” VARCHAR2(240),
“ITF_CLASS” VARCHAR2(240),
“SEND_MSG” CLOB,
“RECEIVE_MSG” CLOB,
“SERVICE_NAME” VARCHAR2(10),
“EXCEPTION_MSG” VARCHAR2(4000),
“CREATE_DATE” DATE
)
tablespace DATA
partition by range (CREATE_DATE)
(
partition Part_201912 values less than (TO_DATE(‘2020-01-01 00:00:00’)),
partition Part_202001 values less than (TO_DATE(‘2020-02-01 00:00:00’)),
partition Part_202002 values less than (TO_DATE(‘2020-03-01 00:00:00’)),
partition Part_202003 values less than (TO_DATE(‘2020-04-01 00:00:00’)),
partition Part_202004 values less than (TO_DATE(‘2020-05-01 00:00:00’)),
partition Part_202005 values less than (TO_DATE(‘2020-06-01 00:00:00’)),
partition Part_202006 values less than (TO_DATE(‘2020-07-01 00:00:00’)),
partition Part_202007 values less than (TO_DATE(‘2020-08-01 00:00:00’)),
partition Part_202008 values less than (TO_DATE(‘2020-09-01 00:00:00’)),
partition Part_MAX values less than (maxvalue)
);

Insert into DBMT.RESOURCES_S_MSG_new select /+parallel 8 index(t IDX_CREATE_TIME)/ * from DBMT.RESOURCES_S_MSG t ;

SQL> select count(*) from dbmt.RESOURCES_S_MSG_NEW partition(PART_202003);

COUNT(*)

    31

SQL> select count(*) from dbmt.RESOURCES_S_MSG_NEW partition(PART_202001);

COUNT(*)

    10