文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> 模式对象>

分享
更新时间: 2019/08/09 GMT+08:00

CREATE TABLE

Oracle的CREATE TABLE语句用于创建表。目标数据库支持Oracle的CREATE TABLES,无需进行迁移。

ALTER TABLE

Oracle的ALTER TABLE语句用于新增、重命名、修改或删除一个表中的列。目标数据库支持Oracle的ALTER TABLES,无需进行迁移。

PRIMARY KEY

Oracle中如果存在同一张表的CREATE TABLE和ALTER TABLE语句,无论这些语句是否在同一个脚本中,迁移时均会合并为该表的CREATE TABLE语句。迁移完成后,需要对相应的DDL脚本进行迁移。

输入:PRIMARY KEY

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;

输出

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语句包含约束,如果直接使用会报错:Cannot create index whose evaluation cannot be enforced to remote nodes.

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

输入

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) ;

输出

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 ;

输入

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;
/

输出

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

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;

输出

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 */
;

ENABLE

GaussDB不支持在ALTER TABLE MODIFY语句中使用ENABLE,因此ENABLE将会在迁移过程中被注释掉。

输入:ENABLE

  CREATE TABLE BASE_APPR_FLOW 
    ( FLOW_ID VARCHAR2(5), 
  FLOW_NAME VARCHAR2(60), 
  FLOW_TYPE VARCHAR2(4), 
  FIELD1 NUMBER(5,0), 
  FIELD2 NUMBER(5,0), 
  NOTES VARCHAR2(40), 
  LST_USER_NO VARCHAR2(9), 
  LST_USER_STRUCD NUMBER(10,0), 
  LST_CHG_DATE VARCHAR2(10), 
  FLOW_CONTROL VARCHAR2(200), 
  CONT_TYPE VARCHAR2(2)
    ) 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 SPMS_DATA ;
 --------------------------------------------------------
 --  Constraints for Table BASE_APPR_FLOW
 --------------------------------------------------------

   ALTER TABLE BASE_APPR_FLOW MODIFY (FLOW_ID NOT NULL ENABLE);
   ALTER TABLE BASE_APPR_FLOW MODIFY (FLOW_NAME NOT NULL ENABLE);
   ALTER TABLE BASE_APPR_FLOW MODIFY (FLOW_TYPE NOT NULL ENABLE);
   ALTER TABLE BASE_APPR_FLOW MODIFY (LST_USER_NO NOT NULL ENABLE);
   ALTER TABLE BASE_APPR_FLOW MODIFY (LST_CHG_DATE NOT NULL ENABLE);
   ALTER TABLE BASE_APPR_FLOW ADD CONSTRAINT PK_BASE_APPR_FLOW PRIMARY KEY (FLOW_ID)
   USING INDEX 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  ENABLE;

输出

CREATE
      TABLE
           BASE_APPR_FLOW (
                FLOW_ID VARCHAR2 (5)
                ,FLOW_NAME VARCHAR2 (60)
                ,FLOW_TYPE VARCHAR2 (4)
                ,FIELD1 NUMBER (
                     5
                     ,0
                )
                ,FIELD2 NUMBER (
                     5
                     ,0
                )
                ,NOTES VARCHAR2 (40)
                ,LST_USER_NO VARCHAR2 (9)
                ,LST_USER_STRUCD NUMBER (
                     10
                     ,0
                )
                ,LST_CHG_DATE VARCHAR2 (10)
                ,FLOW_CONTROL VARCHAR2 (200)
                ,CONT_TYPE VARCHAR2 (2)
                ,CONSTRAINT PK_BASE_APPR_FLOW PRIMARY KEY (FLOW_ID)
           ) /*SEGMENT CREATION IMMEDIATE*/
           /*PCTFREE 10*/
           /*PCTUSED 40*/
           /*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 */
 ;

 /*  */
 /*   Constraints for Table BASE_APPR_FLOW */
 /*  */
 ALTER TABLE
      BASE_APPR_FLOW ALTER FLOW_ID
 SET
      NOT NULL
 ;

 ALTER TABLE
      BASE_APPR_FLOW ALTER FLOW_NAME
 SET
      NOT NULL
 ;

 ALTER TABLE
      BASE_APPR_FLOW ALTER FLOW_TYPE
 SET
      NOT NULL
 ;

 ALTER TABLE
      BASE_APPR_FLOW ALTER LST_USER_NO
 SET
      NOT NULL
 ;

 ALTER TABLE
      BASE_APPR_FLOW ALTER LST_CHG_DATE
 SET
      NOT NULL
 ;

分区

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

图1 表的分区和子分区

  

Migration Tool支持范围分区。

该工具不支持如下分区/子分区(在迁移脚本中会被注释掉):

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

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

  • PARTITION by HASH

    Hash分区是一种分区技术,其中Hash算法用于在不同分区(子表)之间均匀分配行。通常在无法进行范围分区时使用该技术,例如通过员工ID、产品ID等进行分区。Migration Tool不支持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

    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'))
      );
    输出
    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

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

    输入:LIST PARTITION

    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) );

    输出

    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(指定存储参数)

    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 )
            );

    输出

    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;

    输出

    CREATE TABLE tab1_list 
     AS 
     ( SELECT *
       FROM  tab1 );

    输入:LIST PARTITION和SUBPARTITIONS

    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;

    输出

    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

    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' )
        );

    输出

    CREATE TABLE q1_sales_by_region 
              ( deptno NUMBER
              , deptname varchar2 (20)
              , quarterly_sales NUMBER (10,2)
              , state varchar2 (2)
              );
  • PARTITION by RANGE

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

输入:RANGE PARTITION(指定存储参数)

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 )*/

输出

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

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));

输出

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
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));

输出

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约束

Range/哈希/列表分区的CREATE TABLE语句,添加约束会产生如下错误:

分区表的PRIMARY KEY/UNIQUE约束无效

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

脚本: wo_integrate_log_t.sql, wo_change_log_t.sql

输入:

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);

输出:

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 ;

输入

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);

输出

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);

SEGMENT CREATION

Gauss不支持SEGMENT CREATION { IMMEDIATE | DEFERRED },因此该语句将会在迁移过程中被注释掉。设置commentStorageParameter=true后,将会被注释掉

输入:SEGMENT CREATION

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 ;

输出

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

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

输入:指定TABLE和STORAGE

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)) ;

Output

  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 */
 ;
说明:

如果commment_storage_parameter设置 true,存储参数在迁移过程中将会被注释掉。

STORE

Gauss不支持LOB列中的STORE关键字,因此该关键字在迁移过程中将会被注释掉。

输入:指定TABLE和STORE

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)) ;

输出

 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

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 );*/

输出

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-clause(REFERENCE-子句)”建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外部约束是关系属性子句或对象属性子句中的一部分。

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

Migration tool支持内联和外联外键约束,如下所示。

输入:在CREATE TABLE中使用外键和内联约束

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 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中使用外键和外联约束

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
);

输出

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 */
);

number(*,n)

number(*,n) 迁移为 number(38,n)。

输入: number(*,n)

CREATE TABLE NUM1 
(STATEMENT_ID VARCHAR2(30), 
 PLAN_ID NUMBER, 
 TIMESTAMP DATE, 
 REMARKS VARCHAR2(4000), 
 OPERATION VARCHAR2(30), 
 OBJECT_INSTANCE NUMBER(*,0), 
 OBJECT_TYPE VARCHAR2(30), 
 OPTIMIZER VARCHAR2(255), 
 SEARCH_COLUMNS NUMBER, 
 ID NUMBER(*,0), 
 PARENT_ID NUMBER(*,0), 
 DEPTH NUMBER(*,0), 
 POSITION NUMBER(*,0), 
 COST NUMBER(*,0), 
 CARDINALITY NUMBER(*,0), 
 BYTES NUMBER(*,0), 
 OTHER_TAG VARCHAR2(255), 
 PARTITION_START VARCHAR2(255), 
 PARTITION_STOP VARCHAR2(255), 
 PARTITION_ID NUMBER(*,0), 
 OTHER LONG, 
 DISTRIBUTION VARCHAR2(30), 
 CPU_COST NUMBER(*,0), 
 IO_COST NUMBER(*,0), 
 TEMP_SPACE NUMBER(*,0), 
 FILTER_PREDICATES VARCHAR2(4000), 
 PROJECTION VARCHAR2(4000), 
 TIME NUMBER(*,0), 
 OTHER_XML CLOB
 ) ;

输出

CREATE TABLE
     NUM1 (
          STATEMENT_ID VARCHAR2 (30)
          ,PLAN_ID NUMBER
          ,TIMESTAMP DATE
          ,REMARKS VARCHAR2 (4000)
          ,OPERATION VARCHAR2 (30)
          ,OBJECT_INSTANCE NUMBER (38,0)
          ,OBJECT_TYPE VARCHAR2 (30)
          ,OPTIMIZER VARCHAR2 (255)
          ,SEARCH_COLUMNS NUMBER
          ,ID NUMBER (38,0)
          ,PARENT_ID NUMBER (38,0)
          ,DEPTH NUMBER (38,0)
          ,POSITION NUMBER (38,0)
          ,COST NUMBER (38,0)
          ,CARDINALITY NUMBER (38,0)
          ,BYTES NUMBER (38,0)
          ,OTHER_TAG VARCHAR2 (255)
          ,PARTITION_START VARCHAR2 (255)
          ,PARTITION_STOP VARCHAR2 (255)
          ,PARTITION_ID NUMBER (38,0)
          ,OTHER TEXT
          ,DISTRIBUTION VARCHAR2 (30)
          ,CPU_COST NUMBER (38,0)
          ,IO_COST NUMBER (38,0)
          ,TEMP_SPACE NUMBER (38,0)
          ,FILTER_PREDICATES VARCHAR2 (4000)
          ,PROJECTION VARCHAR2 (4000)
          ,TIME NUMBER (38,0)
          ,OTHER_XML CLOB
     );

LONG数据类型

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

输入:TABLE结构中使用LONG数据类型

CREATE TABLE project ( proj_cd INT
                       , proj_name VARCHAR2(32)
                       , dept_no INT
                       , proj_det LONG );

输出

CREATE TABLE project ( proj_cd INT
                        , proj_name VARCHAR2(32)
                        , dept_no INT
                        , proj_det TEXT );

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

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; 
/

输出

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;
/

附加日志数据

支持在redo日志文件中记录额外字段。而记录这些额外字段的过程称为附加日志记录。 Oracle支持此功能,Gauss不支持。

输入

CREATE TABLE sad.fnd_lookup_values_t
           (
   lookup_code_id NUMBER NOT NULL /* ENABLE */
   ,lookup_code VARCHAR2 (40) NOT NULL /* ENABLE */
   ,meaning       VARCHAR2 (100) 
   ,other_meaning VARCHAR2 (100) 
   ,order_by_no   NUMBER 
   ,start_time    DATE DEFAULT SYSDATE NOT NULL /* ENABLE */
   ,end_time    DATE 
   ,enable_flag CHAR( 1 ) DEFAULT 'Y' NOT NULL /* ENABLE */
   ,disable_date DATE 
   ,created_by   NUMBER ( 15 ,0 ) NOT NULL /* ENABLE */
   ,creation_date DATE NOT NULL /* ENABLE */
   ,last_updated_by NUMBER ( 15 ,0 ) NOT NULL /* ENABLE */
   ,last_update_date DATE NOT NULL /* ENABLE */
   ,last_update_login NUMBER ( 15 ,0 ) DEFAULT 0 NOT NULL /* ENABLE */
   ,description    VARCHAR2 (500) 
   ,lookup_type_id NUMBER NOT NULL/* ENABLE */
   ,attribute4 VARCHAR2 (250) 
   ,supplemental log data (ALL) COLUMNS
   ) ;

输出

CREATE TABLE sad.fnd_lookup_values_t
           (
   lookup_code_id NUMBER NOT NULL /* ENABLE */
   ,lookup_code VARCHAR2 (40) NOT NULL /* ENABLE */
   ,meaning       VARCHAR2 (100) 
   ,other_meaning VARCHAR2 (100) 
   ,order_by_no   NUMBER 
   ,start_time    DATE DEFAULT SYSDATE NOT NULL /* ENABLE */
   ,end_time    DATE 
   ,enable_flag CHAR( 1 ) DEFAULT 'Y' NOT NULL /* ENABLE */
   ,disable_date DATE 
   ,created_by   NUMBER ( 15 ,0 ) NOT NULL /* ENABLE */
   ,creation_date DATE NOT NULL /* ENABLE */
   ,last_updated_by NUMBER ( 15 ,0 ) NOT NULL /* ENABLE */
   ,last_update_date DATE NOT NULL /* ENABLE */
   ,last_update_login NUMBER ( 15 ,0 ) DEFAULT 0 NOT NULL /* ENABLE */
   ,description    VARCHAR2 (500) 
   ,lookup_type_id NUMBER NOT NULL/* ENABLE */
   ,attribute4 VARCHAR2 (250) 
   /* ,supplemental log data (ALL) COLUMNS */
   ) ;
说明:

Gauss不支持附加日志数据,需要注释。

支持COMPRESS短语

输入:COMPRESS短语

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
NOCOMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

输出

CREATE
     TABLE
          test_tab (
               id NUMBER (10) NOT NULL
               ,description VARCHAR2 (100) NOT NULL
               ,created_date DATE NOT NULL
               ,created_by VARCHAR2 (50) NOT NULL
               ,updated_date DATE
               ,updated_by VARCHAR2 (50)
          ) /*NOCOMPRESS*/
          PARTITION BY RANGE (created_date) (
               PARTITION test_tab_q1
               VALUES LESS THAN (
                    TO_DATE( '01/04/2003' ,'DD/MM/YYYY' )
               ) /*COMPRESS*/
               ,PARTITION test_tab_q2
               VALUES LESS THAN (MAXVALUE)
) ;

支持bitmap索引

该功能通过 BitmapIndexSupport 设置,迁移过程中默认bitmap索引被注释掉。

输入:bitmap索引

CREATE BITMAP INDEX 
emp_bitmap_idx
ON index_demo (gender);

输出

/*CREATE BITMAP INDEX emp_bitmap_idx ON index_demo (gender);*/

如果 BitmapIndexSupport设置为BTREE,迁移结果如下:

输出

CREATE
     /*bitmap*/
     INDEX emp_bitmap_idx
          ON index_demo
          USING btree (gender) ;

支持自定义表空间

输入: 自定义表空间

CREATE
     TABLE
          SEAS_VERSION_DDL_REL_ORA (
               VERSION_ORA_ID VARCHAR2 (20)
               ,TAB_OBJ_ID VARCHAR2 (20)
               ,AUDIT_ID VARCHAR2 (20)
               ,DDL_SYS CLOB
               ,DDL_USER CLOB
               ,IF_CONFORM VARCHAR2 (3)
               ,DDL_TYPE_SYS VARCHAR2 (5)
               ,DDL_REN_REASON_SYS VARCHAR2 (4000)
               ,DDL_ERR_SYS VARCHAR2 (4000)
          ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE (
               INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
          ) TABLESPACE DRMS LOB (DDL_SYS) STORE AS BASICFILE (
               TABLESPACE DRMS 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 (DDL_USER) STORE AS BASICFILE (
               TABLESPACE DRMS 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
               )
          )

输出

CREATE
     TABLE
          SEAS_VERSION_DDL_REL_ORA (
               VERSION_ORA_ID VARCHAR2 (20)
               ,TAB_OBJ_ID VARCHAR2 (20)
               ,AUDIT_ID VARCHAR2 (20)
               ,DDL_SYS CLOB
               ,DDL_USER CLOB
               ,IF_CONFORM VARCHAR2 (3)
               ,DDL_TYPE_SYS VARCHAR2 (5)
               ,DDL_REN_REASON_SYS VARCHAR2 (4000)
               ,DDL_ERR_SYS VARCHAR2 (4000)
          ) /*SEGMENT CREATION IMMEDIATE*/
          /*PCTFREE 10*/
          /*PCTUSED 40*/
          /*INITRANS 1*/
          /*MAXTRANS 255*/
          /*NOCOMPRESS*/
          /*LOGGING*/
          /*STORAGE(INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
          /*TABLESPACE DRMS */
          /*LOB (DDL_SYS) STORE AS BASICFILE ( TABLESPACE DRMS 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))*/
          /*LOB (DDL_USER) STORE AS BASICFILE ( TABLESPACE DRMS 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))*/
;

SUPPLEMENTAL LOG DATA

表创建中的“SUPPLEMENTAL LOG DATA”不受支持,因此需要注释掉。

输入

 CREATE TABLE SAD.FND_DATA_CHANGE_LOGS_T
   (    LOGID NUMBER,
        TABLE_NAME VARCHAR2(40) NOT NULL ENABLE,
        TABLE_KEY_COLUMNS VARCHAR2(200),
        TABLE_KEY_VALUES VARCHAR2(200),
        COLUMN_NAME VARCHAR2(40) NOT NULL ENABLE,
        COLUMN_CHANGE_FROM_VALUE VARCHAR2(200),
        COLUMN_CHANGE_TO_VALUE VARCHAR2(200),
        DESCRIPTION VARCHAR2(500),
         SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   );

输出

CREATE TABLE sad.fnd_data_change_logs_t 
  ( 
     logid                    NUMBER 
     ,table_name               VARCHAR2 (40) NOT NULL /* ENABLE */ 
     ,table_key_columns        VARCHAR2 (200)
     ,table_key_values         VARCHAR2 (200) 
     ,column_name              VARCHAR2 (40) NOT NULL /* ENABLE */ 
     ,column_change_from_value VARCHAR2 (200)
     ,column_change_to_value   VARCHAR2 (200) 
     ,description              VARCHAR2 (500) 
     /*, SUPPLEMENTAL LOG DATA (ALL) COLUMNS*/ 
  )

LONG RAW

表创建中的“数据类型LONG RAW”不受支持,需要用Bytea来替换Long Raw数据类型。

输入

CREATE TABLE SAD.HW_WORKFLOWDEFS
   ( ID NUMBER(*,0),
 WF_NAME VARCHAR2(200),
 WF_DEFINITION LONG RAW,
 WF_VERSION NUMBER(*,0),
 WF_PUBLISH CHAR(1),
 WF_MAINFLOW CHAR(1),
 WF_APP_NAME VARCHAR2(20),
 CREATED_BY NUMBER,
 CREATION_DATE DATE,
 LAST_UPDATED_BY NUMBER,
 LAST_UPDATE_DATE DATE,
 WFDESC VARCHAR2(2000)
   );

输出

CREATE TABLE sad.hw_workflowdefs 
  ( 
     id                    NUMBER (38, 0), 
     wf_name          VARCHAR2 (200), 
     wf_definition     BYTEA, 
     wf_version       NUMBER (38, 0), 
     wf_publish       CHAR(1), 
     wf_mainflow     CHAR(1), 
     wf_app_name   VARCHAR2 (20), 
     created_by       NUMBER, 
     creation_date    DATE, 
     last_updated_by  NUMBER, 
     last_update_date DATE, 
     wfdesc               VARCHAR2 (2000) 
  ); 

SYS_GUID

SYS_GUID是内嵌函数,返回表中某一行的全域唯一识别元(GUID)。SYS_GUID不使用参数,返回一个16字节的RAW值。

Input

CREATE TABLE sad.fnd_data_change_logs_t
  (
    logid                    NUMBER,
    table_name               VARCHAR2 (40) NOT NULL /* ENABLE */
    ,table_key_columns        VARCHAR2 (200),
 table_key_values         VARCHAR2 (200),
 column_name              VARCHAR2 (40) NOT NULL /* ENABLE */
 ,column_change_from_value VARCHAR2 (200),
 column_change_to_value   VARCHAR2 (200),
 organization_id          NUMBER,
 created_by               NUMBER (15, 0) NOT NULL /* ENABLE */
 ,creation_date            DATE NOT NULL /* ENABLE */
 ,last_updated_by          NUMBER (15, 0) NOT NULL /* ENABLE */
 ,last_update_date         DATE NOT NULL /* ENABLE */
 ,last_update_login        NUMBER (15, 0) DEFAULT 0 NOT NULL /* ENABLE */
 ,description              VARCHAR2 (500),
 sys_id                   VARCHAR2 (32) DEFAULT Sys_guid( )
 /*, SUPPLEMENTAL LOG DATA (ALL) COLUMNS*/
  ); 

Output

CREATE TABLE sad.fnd_data_change_logs_t
  (
    logid                    NUMBER,
    table_name               VARCHAR2 (40) NOT NULL /* ENABLE */
    ,table_key_columns        VARCHAR2 (200),
 table_key_values         VARCHAR2 (200),
 column_name              VARCHAR2 (40) NOT NULL /* ENABLE */
 ,column_change_from_value VARCHAR2 (200),
 column_change_to_value   VARCHAR2 (200),
 organization_id          NUMBER,
 created_by               NUMBER (15, 0) NOT NULL /* ENABLE */
 ,creation_date            DATE NOT NULL /* ENABLE */
 ,last_updated_by          NUMBER (15, 0) NOT NULL /* ENABLE */
 ,last_update_date         DATE NOT NULL /* ENABLE */
 ,last_update_login        NUMBER (15, 0) DEFAULT 0 NOT NULL /* ENABLE */
 ,description              VARCHAR2 (500),
 sys_id                   VARCHAR2 (32) DEFAULT MIG_ORA_EXT.Sys_guid( )
 /*, SUPPLEMENTAL LOG DATA (ALL) COLUMNS*/
  ); 
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区