文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ 模式对象/ 表（Oracle）
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

表（Oracle）

CREATE TABLE

Oracle的CREATE TABLE语句用于创建表。GaussDB(DWS)直接支持该语句，无需迁移。

ALTER TABLE

Oracle的ALTER TABLE语句用于新增、重命名、修改或删除表列。GaussDB(DWS)直接支持该语句，无需迁移。

PRIMARY KEY

Oracle中如果存在两张表具有相同的主键字段，则在执行ALTER TABLE时需加上表名进行区分。

输入：PRIMARY KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE CTP_ARM_CONFIG 
    ( HOSTNAME VARCHAR2(50), 
  OPNAME VARCHAR2(50), 
  PARAMTYPE VARCHAR2(2), 
  PARAMVALUE NUMBER(*,0), 
  MODIFYDATE DATE
    ) SEGMENT CREATION DEFERRED 
   PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
   STORAGE( PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA ;

 ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (HOSTNAME, OPNAME)
   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
   STORAGE( PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE SPMS_DATA  ENABLE;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE
      TABLE
           CTP_ARM_CONFIG (
                HOSTNAME VARCHAR2 (50)
                ,OPNAME VARCHAR2 (50)
                ,PARAMTYPE VARCHAR2 (2)
                ,PARAMVALUE NUMBER (
                     38
                     ,0
                )
                ,MODIFYDATE DATE
                ,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (
                     HOSTNAME
                     ,OPNAME
                )
           ) /*SEGMENT CREATION DEFERRED*/
           /*PCTFREE 10*/
           /*PCTUSED 0*/
           /*INITRANS 1*/
           /*MAXTRANS 255*/
           /*NOCOMPRESS*/
           /*LOGGING*/
           /*STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
           /*TABLESPACE SPMS_DATA */
 ;

UNIQUE约束

以下ALTER TABLE语句包含约束，如果在GaussDB(DWS)直接调用会报错：Cannot create index whose evaluation cannot be enforced to remote nodes.

该约束迁移和PRIMARY KEY类似。如果已有PRIMARY KEY/UNIQUE约束，无需迁移，保持原样。

输入

1
2
3
4
5
6
7
8
 
CREATE
     TABLE
          GCC_PLAN.T1033 (
               ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL
               ,UDF_FIELD_VALUE_ID NUMBER NOT NULL
          ) ;
ALTER TABLE
     GCC_PLAN.T1033 ADD CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ;

输出

1
2
3
4
5
6
7
 
CREATE TABLE 
        GCC_PLAN.T1033
                             (
                               ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL
                               ,UDF_FIELD_VALUE_ID NUMBER NOT NULL
                               ,CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID)
                              ) ;

NULL约束

在以下包中声明局部变量时不支持NULL约束：

L_CONTRACT_DISTRIBUTE_STATUS SAD_DISTRIBUTION_HEADERS_T.STATUS%TYPE NULL ;

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2(PI_CONTRACT_NUMBER IN VARCHAR2)
  RETURN VARCHAR2 IS
  L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS NULL;

BEGIN

  FOR CUR_CONTRACT IN (SELECT HT.CONTRACT_STATUS
                         FROM SAD_CONTRACTS_V HT
                        WHERE HT.HTH = PI_CONTRACT_NUMBER) 
  LOOP
    IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
      L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel';
    ELSE
      L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS;
    END IF;
  END LOOP;

  RETURN L_CONTRACT_DISTRIBUTE_STATUS;

END CONTRACT_DISTRIBUTE_STATUS_S2;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2 
  ( PI_CONTRACT_NUMBER IN VARCHAR2 )
RETURN VARCHAR2 
PACKAGE 
IS 
 L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS /*NULL*/;
BEGIN
     FOR CUR_CONTRACT IN ( SELECT HT.CONTRACT_STATUS
        FROM SAD_CONTRACTS_V HT
       WHERE HT.HTH = PI_CONTRACT_NUMBER ) 
  LOOP
            IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN
               L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel' ;

   ELSE
    L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS ;

   END IF ;

     END LOOP ;

     RETURN L_CONTRACT_DISTRIBUTE_STATUS ;
END ;
/

未创建索引

如果ALTER TABLE中使用了INDEX或STORAGE参数，需要删掉。需要在CREATE TABLE中添加约束。

输入：PRIMARY KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE CTP_ARM_CONFIG
( HOSTNAME VARCHAR2(50),
OPNAME VARCHAR2(50),
PARAMTYPE VARCHAR2(2),
PARAMVALUE NUMBER(*,0),
MODIFYDATE DATE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE( PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA ;
ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY
(HOSTNAME, OPNAME)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE( PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA ENABLE;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE TABLE
CTP_ARM_CONFIG (
HOSTNAME VARCHAR2 (50)
,OPNAME VARCHAR2 (50)
,PARAMTYPE VARCHAR2 (2)
,PARAMVALUE NUMBER (
38
,0
)
,MODIFYDATE DATE
,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (
HOSTNAME
,OPNAME
)
) /*SEGMENT CREATION DEFERRED*/
/*PCTFREE 10*/
/*PCTUSED 0*/
/*INITRANS 1*/
/*MAXTRANS 255*/
/*NOCOMPRESS*/
/*LOGGING*/
/*STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)*/
/*TABLESPACE SPMS_DATA */
;

分区

大表和索引的维护越来越耗费时间和资源。同时，这些对象会导致数据访问性能明显降低。表和索引的分区可从各方面提升性能、便于维护。

图1 表的分区和子分区

DSC支持范围分区。

该工具不支持以下分区/子分区（在迁移脚本中会被注释掉）：

  • 列表分区
  • Hash分区
  • 范围子分区
  • 列表子分区
  • Hash子分区

未来可能会支持当前不支持的分区/子分区。该工具中，用户可设置配置参数，启用/禁用对不支持语句的注释功能。详情请参见Oracle配置参数。

  • PARTITION BY HASH

    Hash分区是一种分区技术，其中Hash算法用于在不同分区（子表）之间均匀分配行。通常在无法进行范围分区时使用该技术，例如通过员工ID、产品ID等进行分区。DSC不支持PARTITION BY HASH和SUBPARTITION BY HASH，且会注释掉这些语句。

    输入：HASH PARTITION

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) PARTITION BY HASH(deptno) PARTITIONS 16;

    输出

    CREATE TABLE dept ( deptno NUMBER ,deptname VARCHAR( 32 ) ) /* PARTITION BY HASH(deptno) PARTITIONS 16 */ ;

    输入：HASH PARTITION，不使用分区名

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
      PARTITION BY HASH(deptno) PARTITIONS 16;

    输出

    CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
  /*    PARTITION BY HASH(deptno) PARTITIONS 16 */;

    输入：HASH SUBPARTITION

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    CREATE TABLE sales
   ( prod_id       NUMBER(6)
   , cust_id       NUMBER
   , time_id       DATE
   , channel_id    CHAR(1)
   , promo_id      NUMBER(6)
   , quantity_sold NUMBER(3)
   , amount_sold   NUMBER(10,2)
   )
  PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
   SUBPARTITIONS 8STORE IN (ts1, ts2, ts3, ts4)
  ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
  , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
  , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
  , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
  );
    输出 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    CREATE TABLE sales
   ( prod_id       NUMBER(6)
   , cust_id       NUMBER
   , time_id       DATE
   , channel_id    CHAR(1)
   , promo_id      NUMBER(6)
   , quantity_sold NUMBER(3)
   , amount_sold   NUMBER(10,2)
   )
  PARTITION BY RANGE (time_id) /*SUBPARTITION BY HASH (cust_id)
   SUBPARTITIONS 8  STORE IN (ts1, ts2, ts3, ts4) */
  ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
  , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
  , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
  , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
  );
  • PARTITION BY LIST

    列表分区是一种分区技术，在每个分区的说明中指定分区键的离散值列表。DSC不支持PARTITION BY LIST和SUBPARTITION BY LIST，且会注释掉这些语句。

    输入：LIST PARTITION

    1
    		 
    CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR(30), state_code VARCHAR(2), sale_date DATE) STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) ( PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 8M) TABLESPACE tbs8, PARTITION region_west VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING, PARTITION region_south VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES ('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES (NULL), PARTITION region_unknown VALUES (DEFAULT) );

    输出

    1
    		 
    CREATE UNLOGGED TABLE sales_by_region ( item# INTEGER ,qty INTEGER ,store_name VARCHAR( 30 ) ,state_code VARCHAR( 2 ) ,sale_date DATE ) TABLESPACE tbs5 /* PARTITION BY LIST(state_code)(PARTITION region_east VALUES('MA','NY','CT','NH','ME','MD','VA','PA','NJ')  TABLESPACE tbs8, PARTITION region_west VALUES('CA','AZ','NM','OR','WA','UT','NV','CO') , PARTITION region_south VALUES('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES(NULL), PARTITION region_unknown VALUES(DEFAULT) ) */ ;

    输入：LIST PARTITION（使用STORAGE参数）

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    		 
    CREATE TABLE store_master 
          ( Store_id NUMBER
          , Store_address VARCHAR2 (40)
          , City VARCHAR2 (30)
          , State VARCHAR2 (2)
          , zip VARCHAR2 (10)
          , manager_id NUMBER
          ) 
    /*TABLESPACE users*/ 
    STORAGE ( INITIAL 100 k NEXT 100 k 
     PCTINCREASE 0 ) 
   PARTITION BY LIST (city) 
        ( PARTITION south_florida 
          VALUES ( 'MIA', 'ORL' ) 
          /*TABLESPACE users*/ 
          STORAGE ( INITIAL 100 k NEXT 100 
          k PCTINCREASE 0 )
        , PARTITION north_florida 
          VALUES ( 'JAC', 'TAM', 'PEN' ) 
          /*TABLESPACE users*/ 
          STORAGE ( INITIAL 100 k NEXT 100 
          k PCTINCREASE 0 )
        , PARTITION south_georga VALUES 
          ( 'BRU', 'WAY', 'VAL' ) 
          /*TABLESPACE users*/ 
          STORAGE ( INITIAL 100 k NEXT 100 
          k PCTINCREASE 0 )
        , PARTITION north_georgia 
          VALUES ( 'ATL', 'SAV', NULL )
        );

    输出

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    CREATE TABLE store_master 
          ( Store_id NUMBER
          , Store_address VARCHAR2 (40)
          , City VARCHAR2 (30)
          , State VARCHAR2 (2)
          , zip VARCHAR2 (10)
          , manager_id NUMBER
          ) 
    /*TABLESPACE users*/ 
    STORAGE ( INITIAL 100 k NEXT 100 k );

    输入：LIST PARTITION TABLE，基于其他TABLE

    CREATE TABLE tab1_list 
      PARTITION BY LIST (col1) 
         ( partition part1 VALUES ( 1 )
          , partition part2 VALUES ( 2, 
           3, 4 )
          , partition part3 VALUES 
          (DEFAULT)
          ) 
 AS 
 SELECT *
   FROM  tab1;

    输出

    1
2
3
4
    		 
    CREATE TABLE tab1_list 
 AS 
 ( SELECT *
   FROM  tab1 );

    输入：LIST PARTITION，使用SUBPARTITIONS

    1
    		 
    CREATE TABLE big_t_list PARTITION BY LIST(n10) (partition part1 VALUES (1) ,partition part2 VALUES (2,3,4) ,partition part3 VALUES (DEFAULT)) AS SELECT * FROM big_t;

    输出

    1
    		 
    CREATE TABLE big_t_list /* PARTITION BY LIST(n10)(partition part1 VALUES(1) ,partition part2 VALUES(2,3,4) ,partition part3 VALUES(DEFAULT))  */ AS ( SELECT * FROM big_t ) ;

    输入：LIST PARTITION，使用SUBPARTITION TEMPLATE

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    		 
    CREATE TABLE q1_sales_by_region 
          ( deptno NUMBER
          , deptname varchar2 (20)
          , quarterly_sales NUMBER 
          (10,2)
          , state varchar2 (2)
          ) 
   PARTITION BY LIST (state) 
         SUBPARTITION BY RANGE 
         (quarterly_sales) 
         SUBPARTITION TEMPLATE 
         ( SUBPARTITION original VALUES 
         LESS THAN (1001)
         , SUBPARTITION acquired VALUES 
         LESS THAN (8001)
         , SUBPARTITION recent VALUES 
         LESS THAN (MAXVALUE)
         )
    ( PARTITION q1_northwest VALUES 
     ( 'OR', 'WA' )
    , PARTITION q1_southwest VALUES 
     ( 'AZ', 'UT', 'NM' )
    , PARTITION q1_northeast VALUES 
     ( 'NY', 'VM', 'NJ' )
    , PARTITION q1_southcentral VALUES 
     ( 'OK', 'TX' )
    );

    输出

    1
2
3
4
5
6
    		 
    CREATE TABLE q1_sales_by_region 
          ( deptno NUMBER
          , deptname varchar2 (20)
          , quarterly_sales NUMBER (10,2)
          , state varchar2 (2)
          );
  • PARTITION BY RANGE

    范围分区是一种分区技术，将不同范围数据分别存储在不同的子表中。当用户需要将不同范围的数据（例如日期字段）存储在一起时，范围分区很有用。DSC支持 PARTITION BY RANGE，不支持SUBPARTITION BY RANGE，且会注释掉该语句。

    输入：RANGE PARTITION（使用STORAGE参数）

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    		 
    CREATE
     TABLE
          CCM_TA550002_H (
               STRU_ID VARCHAR2 (10)
               ,ORGAN1_NO VARCHAR2 (10)
               ,ORGAN2_NO VARCHAR2 (10)
          ) partition BY range (ORGAN2_NO) (
               partition CCM_TA550002_01
               VALUES LESS than ('00100') /* TABLESPACE users */
               /*pctfree 10*/
               /*initrans 1*/
               /*storage(initial 256 K NEXT 256 K minextents 1 maxextents unlimited  )*/
               ,partition CCM_TA550002_02
               VALUES LESS than ('00200') /* TABLESPACE users */
               /*pctfree 10*/
               /*initrans 1*/
               /* storage ( initial 256 K NEXT
256K  minextents 1
maxextents unlimited
pctincrease 0 )*/

    输出

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    CREATE TABLE CCM_TA550002_H 
          ( STRU_ID VARCHAR2 (10)
          , ORGAN1_NO VARCHAR2 (10)
          , ORGAN2_NO VARCHAR2 (10)
          ) 
    partition BY range (ORGAN2_NO) 
             ( partition CCM_TA550002_01 VALUES LESS 
               than ('00100') 
               /*TABLESPACE users*/
             , partition CCM_TA550002_02 VALUES LESS 
               than ('00200') 
               /*TABLESPACE users*/
             );

    输入：RANGE PARTITION，使用SUBPARTITIONS

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    CREATE TABLE composite_rng_list (
cust_id     NUMBER(10), 
cust_name   VARCHAR2(25), 
cust_state  VARCHAR2(2), 
time_id     DATE)
PARTITION BY RANGE(time_id) 
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) ( 
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

    输出

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    CREATE TABLE composite_rng_list (
cust_id     NUMBER(10), 
cust_name   VARCHAR2(25), 
cust_state  VARCHAR2(2), 
time_id     DATE)
PARTITION BY RANGE(time_id) 
/*SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3)*/ ( 
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));
    输入：RANGE PARTITION，使用SUBPARTITION TEMPLATE 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    CREATE TABLE composite_rng_rng (
cust_id     NUMBER(10), 
cust_name   VARCHAR2(25), 
cust_state  VARCHAR2(2), 
time_id     DATE)
PARTITION BY RANGE(time_id) 
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) ( 
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));

    输出

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    CREATE TABLE composite_rng_rng (
cust_id     NUMBER(10), 
cust_name   VARCHAR2(25), 
cust_state  VARCHAR2(2), 
time_id     DATE)
PARTITION BY RANGE(time_id) 
/*SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3)*/ ( 
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));

    分区表的PRIMARY KEY/UNIQUE约束

    如果CREATE TABLE语句包含范围/Hash/列表分区，则添加约束会产生如下错误：

    Invalid PRIMARY KEY/UNIQUE constraint for partitioned table

    注意：PRIMARY KEY/UNIQUE约束所在的列必须包含PARTITION KEY。

    脚本： wo_integrate_log_t.SQL, wo_change_log_t.SQL

    输入：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
    		 
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
(
LOG_ID            NUMBER not null,
PROJECT_NUMBER    VARCHAR2(40),
MESSAGE_ID        VARCHAR2(100),
BUSINESS_ID       VARCHAR2(100),
BUSINESS_TYPE     VARCHAR2(100),
INTEGRATE_CONTENT CLOB,
OPERATION_RESULT  VARCHAR2(100),
FAILED_MSG        VARCHAR2(4000),
HOST_NAME         VARCHAR2(100) not null,
CREATED_BY        NUMBER not null,
CREATION_DATE     DATE not null,
LAST_UPDATED_BY   NUMBER not null,
LAST_UPDATE_DATE  DATE not null,
SOURCE_CODE       VARCHAR2(100),
TENANT_ID         NUMBER
)
partition by range (CREATION_DATE)
(
partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA
);
alter table SD_WO.WO_INTEGRATE_LOG_T
add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);

    输出：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
    		 
    CREATE TABLE
SD_WO.WO_INTEGRATE_LOG_T (
LOG_ID NUMBER NOT NULL
,PROJECT_NUMBER VARCHAR2 (40)
,MESSAGE_ID VARCHAR2 (100)
,BUSINESS_ID VARCHAR2 (100)
,BUSINESS_TYPE VARCHAR2 (100)
,INTEGRATE_CONTENT CLOB
,OPERATION_RESULT VARCHAR2 (100)
,FAILED_MSG VARCHAR2 (4000)
,HOST_NAME VARCHAR2 (100) NOT NULL
,CREATED_BY NUMBER NOT NULL
,CREATION_DATE DATE NOT NULL
,LAST_UPDATED_BY NUMBER NOT NULL
,LAST_UPDATE_DATE DATE NOT NULL
,SOURCE_CODE VARCHAR2 (100)
,TENANT_ID NUMBER
,CONSTRAINT WO_INTEGRATE_LOG_PK PRIMARY KEY (LOG_ID)
) partition BY range (CREATION_DATE) (
partition P2018
VALUES LESS than (
TO_DATE( ' 2018-10-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P53873
VALUES LESS than (
TO_DATE( ' 2018-11-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P104273
VALUES LESS than (
TO_DATE( ' 2018-12-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P105533
VALUES LESS than (
TO_DATE( ' 2019-01-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P108153
VALUES LESS than (
TO_DATE( ' 2019-02-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P127173
VALUES LESS than (
TO_DATE( ' 2019-03-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P130313
VALUES LESS than (
TO_DATE( ' 2019-04-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
) ;
CREATE
index WO_INTEGRATE_LOG_N1
ON SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID) LOCAL ;
CREATE
index WO_INTEGRATE_LOG_N2
ON SD_WO.WO_INTEGRATE_LOG_T (
CREATION_DATE
,BUSINESS_TYPE
) LOCAL ;
CREATE
index WO_INTEGRATE_LOG_N3
ON SD_WO.WO_INTEGRATE_LOG_T (
PROJECT_NUMBER
,BUSINESS_TYPE
) LOCAL ;

    输入：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
    		 
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
(
  LOG_ID            NUMBER not null,
  PROJECT_NUMBER    VARCHAR2(40),
  MESSAGE_ID        VARCHAR2(100),
  BUSINESS_ID       VARCHAR2(100),
  BUSINESS_TYPE     VARCHAR2(100),
  INTEGRATE_CONTENT CLOB,
  OPERATION_RESULT  VARCHAR2(100),
  FAILED_MSG        VARCHAR2(4000),
  HOST_NAME         VARCHAR2(100) not null,
  CREATED_BY        NUMBER not null,
  CREATION_DATE     DATE not null,
  LAST_UPDATED_BY   NUMBER not null,
  LAST_UPDATE_DATE  DATE not null,
  SOURCE_CODE       VARCHAR2(100),
  TENANT_ID         NUMBER
)
partition by range (CREATION_DATE)
(
  partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA
);

alter table SD_WO.WO_INTEGRATE_LOG_T
  add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);

    输出：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
    		 
    CREATE TABLE SD_WO.WO_INTEGRATE_LOG_T
(
  LOG_ID            NUMBER not null,
  PROJECT_NUMBER    VARCHAR2(40),
  MESSAGE_ID        VARCHAR2(100),
  BUSINESS_ID       VARCHAR2(100),
  BUSINESS_TYPE     VARCHAR2(100),
  INTEGRATE_CONTENT CLOB,
  OPERATION_RESULT  VARCHAR2(100),
  FAILED_MSG        VARCHAR2(4000),
  HOST_NAME         VARCHAR2(100) not null,
  CREATED_BY        NUMBER not null,
  CREATION_DATE     DATE not null,
  LAST_UPDATED_BY   NUMBER not null,
  LAST_UPDATE_DATE  DATE not null,
  SOURCE_CODE       VARCHAR2(100),
  TENANT_ID         NUMBER
)
partition by range (CREATION_DATE)
(
  partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA,
  partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SDWO_DATA
);

ALTER TABLE SD_WO.WO_INTEGRATE_LOG_T
  add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);

数据类型

删除数据类型中的BYTE关键字。

Oracle语法

迁移后语法

 
1
2
3
4
5
6
 
CREATE TABLE TBL_ORACLE
 (
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  );

1
2
3
4
5
6
 
CREATE TABLE  TBL_ORACLE
 (
     ID NUMBER
     ,Name VARCHAR2 (100)
     ,ADDRESS VARCHAR2 (200)
 ) ;

分区（注释分区）

oracle配置参数中“#分区表唯一或主键约束”为“comment_partition”。

Oracle语法

迁移后语法

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE TABLE TBL_ORACLE
(
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  )
TABLESPACE space1
PCTUSED    40
PCTFREE    0
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (ID)
(  
  PARTITION PART_2010 VALUES LESS THAN (10)
    NOLOGGING, 
  PARTITION PART_2011 VALUES LESS THAN (20)
    NOLOGGING ,  
  PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
)
ENABLE ROW MOVEMENT;


ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
CREATE UNLOGGED TABLE  TBL_ORACLE 
(
               ID NUMBER
               ,Name VARCHAR2 (100)
               ,ADDRESS VARCHAR2 (200)
               ,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)
) 
 TABLESPACE space1
          /*PCTUSED 40*/
          PCTFREE 0
          INITRANS 1
          MAXTRANS 255
 /* PARTITION BY RANGE(ID)(PARTITION PART_2010 VALUES LESS THAN(10) , 
PARTITION PART_2011 VALUES LESS THAN(20)  , PARTITION PART_2012 VALUES LESS THAN(MAXVALUE)  ) 
ENABLE ROW MOVEMENT */
 ;

分区（注释约束）

oracle配置参数中“#分区表唯一或主键约束”为“comment_unique”。

Oracle语法

迁移后语法

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE TABLE TBL_ORACLE
(
   ID     Number,
   Name   VARCHAR2(100 BYTE),
  ADDRESS VARCHAR2(200 BYTE)
  )
TABLESPACE space1
PCTUSED    40
PCTFREE    0
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (ID)
(  
  PARTITION PART_2010 VALUES LESS THAN (10)
    NOLOGGING, 
  PARTITION PART_2011 VALUES LESS THAN (20)
    NOLOGGING ,  
  PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
)
ENABLE ROW MOVEMENT;


ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE UNLOGGED TABLE TBL_ORACLE 
(
               ID NUMBER
               ,Name VARCHAR2 (100)
               ,ADDRESS VARCHAR2 (200) 
/*,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)*/
) 
 TABLESPACE space1 
          /*PCTUSED 40*/
          PCTFREE 0
          INITRANS 1
          MAXTRANS 255
PARTITION BY RANGE (ID) 
(
      PARTITION PART_2010  VALUES LESS THAN (10)
     ,PARTITION PART_2011  VALUES LESS THAN (20)
     ,PARTITION PART_2012  VALUES LESS THAN (MAXVALUE)
          ) ENABLE ROW MOVEMENT ;

分区（一）

在非分区表中，为表“ALTER TABLE TRUNCATE PARTITION”添加注释。

Oracle语法

迁移后语法

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
CREATE TABLE product_range
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
  partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    pctfree 10
    initrans 1
);

CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
(
  partition P_2020 VALUES (2020)
    pctfree 10
    initrans 1
);


CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
CREATE TABLE product_range
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
  partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    pctfree 10
    initrans 1
);

CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
(
  partition P_2020 VALUES (2020)
    pctfree 10
    initrans 1
)*/;


CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
/*EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;*/
NULL;
END;
/

分区（二）

在非分区表中，删除表“ALTER TABLE TRUNCATE PARTITION”中的数据。

Oracle语法

迁移后语法

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) );

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_2020;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) )*/;


CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 /* EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; */
 IF 'PART_' || V_ID = 'PART_2015' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2011,2012,2013,2014,2015);
 ELSIF 'PART_' || V_ID = 'PART_2016' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2016);
 ELSIF 'PART_' || V_ID = 'PART_2017' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2017);
 ELSIF 'PART_' || V_ID = 'PART_2018' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2018);
 ELSIF 'PART_' || V_ID = 'PART_2019' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2019);
 ELSIF 'PART_' || V_ID = 'PART_2020' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2020);
 ELSE
    DELETE FROM product_list WHERE Year_Manufacture NOT IN (2011,2012,2013,2014,2015,2016,2017,2018,2019,2020);
 END IF;
 NULL;
END;
/

SEGMENT CREATION

GaussDB(DWS)不支持SEGMENT CREATION { IMMEDIATE | DEFERRED }，因此该语句在迁移后被注释掉，需要设置commentStorageParameter=true。

输入：TABLE，使用SEGMENT CREATION

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
CREATE TABLE T1 
   ( MESSAGE_CODE VARCHAR2(50), 
 MAIL_TITLE VARCHAR2(1000), 
 MAIL_BODY VARCHAR2(1000), 
 MAIL_ADDRESS VARCHAR2(1000), 
 MAIL_ADDRESS_CC VARCHAR2(1000)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE Test ;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
CREATE TABLE T1 
   ( MESSAGE_CODE VARCHAR2(50), 
 MAIL_TITLE VARCHAR2(1000), 
 MAIL_BODY VARCHAR2(1000), 
 MAIL_ADDRESS VARCHAR2(1000), 
 MAIL_ADDRESS_CC VARCHAR2(1000)
   ) /*SEGMENT CREATION DEFERRED */
  /*PCTFREE 10*/
/* PCTUSED 0 */
/*INITRANS 1 */
/*MAXTRANS 255 */
/* NOCOMPRESS LOGGING*/
/*  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/*  TABLESPACE Test */;

STORAGE

GaussDB(DWS)不支持BUFFER_POOL、MAXEXTENTS等存储参数。如果comment_storage_parameter设置为true，出现在表或索引中的这些参数在迁移时会被注释掉。

输入：TABLE，使用STORAGE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
CREATE UNIQUE INDEX PK_BASE_APPR_STEP_DEF ON BASE_APPR_STEP_DEF (FLOW_ID, NODE_ID, STEP_ID) 
   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
   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 SPMS_DATA ;

 CREATE TABLE UFP_MAIL 
    ( MAIL_ID NUMBER(*,0), 
  MAIL_TITLE VARCHAR2(1000), 
  MAIL_BODY VARCHAR2(4000), 
  STATUS VARCHAR2(50), 
  CREATE_TIME DATE, 
  SEND_TIME DATE, 
  MAIL_ADDRESS CLOB, 
  MAIL_CC CLOB, 
  BASE_ID VARCHAR2(20), 
  BASE_STATUS VARCHAR2(50), 
  BASE_VERIFY VARCHAR2(20), 
  BASE_LINK VARCHAR2(4000), 
  MAIL_TYPE VARCHAR2(20), 
  BLIND_COPY_TO CLOB, 
  FILE_NAME VARCHAR2(4000), 
  FULL_FILEPATH VARCHAR2(4000)
    ) SEGMENT CREATION IMMEDIATE 
   PCTFREE 10 PCTUSED 0 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 SPMS_DATA 
  LOB (MAIL_ADDRESS) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE 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)) 
  LOB (MAIL_CC) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE 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)) 
  LOB (BLIND_COPY_TO) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE 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)) ;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
  CREATE
      UNIQUE INDEX PK_BASE_APPR_STEP_DEF
           ON BASE_APPR_STEP_DEF (
           FLOW_ID
           ,NODE_ID
           ,STEP_ID
      ) /*PCTFREE 10*/
      /*INITRANS 2*/
      /*MAXTRANS 255*/
      /*COMPUTE STATISTICS*/
      /*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
      /*TABLESPACE SPMS_DATA */
 ;

如果comment_storage_parameter设为true，存储参数会被注释掉。

STORE

GaussDB(DWS)不支持LOB列的STORE关键字，因此该关键字在迁移后会被注释掉。

输入：TABLE，使用STORE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE TABLE CTP_PROC_LOG 
    ( PORC_NAME VARCHAR2(100), 
  LOG_TIME VARCHAR2(100), 
  LOG_INFO CLOB
    ) SEGMENT CREATION IMMEDIATE 
   PCTFREE 10 PCTUSED 0 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 SPMS_DATA 
  LOB (LOG_INFO) STORE AS BASICFILE (
   TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
   NOCACHE 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)) ;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
 CREATE
      TABLE
           CTP_PROC_LOG (
                PORC_NAME VARCHAR2 (100)
                ,LOG_TIME VARCHAR2 (100)
                ,LOG_INFO CLOB
           ) /*SEGMENT CREATION IMMEDIATE*/
           /*PCTFREE 10*/
           /*PCTUSED 0*/
           /*INITRANS 1*/
           /*MAXTRANS 255*/
           /*NOCOMPRESS*/
           /*LOGGING*/
           /*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
           /*TABLESPACE SPMS_DATA */
           /*LOB (LOG_INFO) STORE AS BASICFILE ( TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))*/
 ;

PCTINCREASE

所有表均不支持存储参数PCTINCREASE。此外，分区表不支持所有存储参数（包括pctfree、minextents和 maxextents）。

输入：TABLE，使用PCTINCREASE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE tab1 (
             col1 < datatype >
           , col2 < datatype >
           , ...
           , colN < datatype > ) 
          TABLESPACE testts 
          PCTFREE 10 INITRANS 1 MAXTRANS 
          255 
          /* STORAGE (
          INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );*/

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE tab1 (
             col1 < datatype >
           , col2 < datatype >
           , ...
           , colN < datatype >  ) 
             TABLESPACE testts 
             PCTFREE 10 INITRANS 1 MAXTRANS 255
             /* STORAGE (
             INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS 
             UNLIMITED );*/

外键

外键在Oracle数据库中用于强制保证引用的完整性。外键意味着一个表中的值必须同时存在另一个表中。被引用的表称为父表，而包含外键的表称为子表。子表中的外键通常会引用父表中的主键。可以在CREATE TABLE或ALTER TABLE语句中定义外键。

必须通过REFERENCE子句建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外联约束是关系属性子句或对象属性子句中的一部分。

如果参数foreignKeyHandler设置为true（默认值），工具将这些语句迁移为注释语句。

DSC支持内联和外联外键约束，如下所示。

输入：CREATE TABLE，使用外键和内联约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE orders (
    order_no    INT  NOT NULL PRIMARY KEY,
    order_date   DATE  NOT NULL,
    cust_id    INT 
 [CONSTRAINT fk_orders_cust]
    REFERENCES customers(cust_id)
   [ON DELETE SET NULL]
 [INITIALLY DEFERRED]
 [ENABLE NOVALIDATE]
);

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CREATE TABLE orders (
    order_no    INT  NOT NULL PRIMARY KEY,
    order_date   DATE  NOT NULL,
    cust_id    INT 
/*
  [CONSTRAINT fk_orders_cust]
   REFERENCES customers(cust_id)
   [ON DELETE SET NULL]
   [INITIALLY DEFERRED]
   [ENABLE NOVALIDATE] */
);

输入：CREATE TABLE，使用外键和外联约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE customers (
        cust_id   INT   NOT NULL,
       cust_name  VARCHAR(64) NOT NULL,
       cust_addr  VARCHAR(256),
 cust_contact_no  VARCHAR(16),
    PRIMARY KEY (cust_id)
);

CREATE TABLE orders (
    order_no    INT  NOT NULL,
    order_date  DATE  NOT NULL,
    cust_id     INT  NOT NULL,
    PRIMARY KEY (order_no),
     CONSTRAINT fk_orders_cust
    FOREIGN KEY (cust_id)
     REFERENCES customers(cust_id)
      ON DELETE CASCADE
);

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE customers (
        cust_id   INT   NOT NULL,
       cust_name  VARCHAR(64) NOT NULL,
       cust_addr  VARCHAR(256),
 cust_contact_no  VARCHAR(16),
    PRIMARY KEY (cust_id)
);

CREATE TABLE orders (
      order_no  INT  NOT NULL,
    order_date  DATE  NOT NULL,
       cust_id  INT  NOT NULL,
    PRIMARY KEY (order_no) /*,
     CONSTRAINT fk_orders_cust
    FOREIGN KEY (cust_id)
     REFERENCES customers(cust_id)
      ON DELETE CASCADE */
);

LONG数据类型

定义为LONG的列可存储变长字符数据，最多可包含2GB信息。MT支持表结构和PL/SQL中的LONG数据类型。

输入：在表结构中使用LONG数据类型

1
2
3
4
 
CREATE TABLE project ( proj_cd INT
                       , proj_name VARCHAR2(32)
                       , dept_no INT
                       , proj_det LONG );

输出：

1
2
3
4
 
CREATE TABLE project ( proj_cd INT
                        , proj_name VARCHAR2(32)
                        , dept_no INT
                        , proj_det TEXT );

输入：在PL/SQL中使用LONG数据类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE OR REPLACE FUNCTION fn_proj_det 
                          ( i_proj_cd INT )
RETURN LONG 
IS 
   v_proj_det LONG;
BEGIN 
    SELECT proj_det
      INTO v_proj_det 
      FROM project
     WHERE proj_cd = i_proj_cd; 

   RETURN v_proj_det; 
END; 
/

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE OR REPLACE FUNCTION fn_proj_det 
                          ( i_proj_cd INT )
RETURN TEXT 
IS 
   v_proj_det TEXT;
BEGIN 
    SELECT proj_det
      INTO v_proj_det 
      FROM project
     WHERE proj_cd = i_proj_cd; 

   RETURN v_proj_det; 
END;
/

TYPE

将“MDSYS.MBRCOORDLIST”替换为“CLOB”。

Oracle语法

迁移后语法

 
1
2
3
4
5
 
create table product_part
(
  partid    VARCHAR2(24),
  mbrcoords MDSYS.MBRCOORDLIST
);

1
2
3
4
5
 
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  mbrcoords CLOB
);

将“MDSYS.SDO_GEOMETRY”替换为“CLOB”。

Oracle语法

迁移后语法

 
1
2
3
4
5
 
create table product_part
(
  partid    VARCHAR2(24),
  shape MDSYS.SDO_GEOMETRY
);

1
2
3
4
5
 
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  shape CLOB
);

将“MDSYS.GEOMETRY”为“CLOB”。

Oracle语法

迁移后语法

 
1
2
3
4
5
 
create table product_part
(
  partid    VARCHAR2(24),
  shape GEOMETRY
);

1
2
3
4
5
 
CREATE TABLE product_part
(
  partid    VARCHAR2(24),
  shape CLOB
);

列

xmax、xmin、left、right、maxvalue为GaussDB(DWS)关键字，这些关键字应全字母大写并加英文双引号（""）。

Oracle语法

迁移后语法

 
1
2
3
4
5
6
7
8
 
create table product
(
  xmax     VARCHAR2(20),
  xmin     VARCHAR2(50),
  left     VARCHAR2(50),
  right    VARCHAR2(50),
  maxvalue VARCHAR2(50)
);

1
2
3
4
5
6
7
8
 
CREATE TABLE product1
(
  "XMAX"     VARCHAR2(20),
  "XMIN"     VARCHAR2(50),
  "LEFT"     VARCHAR2(50),
  "RIGHT"    VARCHAR2(50),
  "MAXVALUE" VARCHAR2(50)
);

间隔分区

对于间隔分区，应该注释分区。

Oracle语法

迁移后语法

 
1
2
3
4
5
6
7
8
9
 
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50),
  manufacture_month DATE
)
partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
  partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));

1
2
3
4
5
6
7
8
9
 
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50),
  manufacture_month DATE
)
/*partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
  partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))*/;
父主题： 模式对象