更新时间：2022-12-08 GMT+08:00
表（Oracle）
CREATE TABLE
Oracle的CREATE TABLE语句用于创建表。GaussDB直接支持该语句，无需迁移
ALTER TABLE
Oracle的ALTER TABLE语句用于新增、重命名、修改或删除表列。GaussDB直接支持该语句，无需迁移。
PRIMARY KEY
Oracle中如果存在两张表具有相同的主键字段，则在执行ALTER 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 */
;
UNIQUE约束
以下ALTER TABLE语句包含约束，如果在GaussDB直接调用会报错：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 */ ;
分区
大表和索引的维护越来越耗费时间和资源。同时，这些对象会导致数据访问性能明显降低。表和索引的分区可从各方面提升性能、便于维护。
图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
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
列表分区是一种分区技术，在每个分区的说明中指定分区键的离散值列表。DSC不支持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（使用STORAGE参数）
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
范围分区是一种分区技术，将不同范围数据分别存储在不同的子表中。当用户需要将不同范围的数据（例如日期字段）存储在一起时，范围分区很有用。DSC支持 PARTITION BY RANGE，不支持SUBPARTITION BY RANGE，且会注释掉该语句。
输入：RANGE PARTITION（使用STORAGE参数）
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 TEMPLATECREATE 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约束
如果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
输入：
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);
数据类型
删除数据类型中的BYTE关键字。
|
Oracle语法 |
迁移后语法 |
|---|---|
CREATE TABLE BL_ORACLE ( ID Number, Name VARCHAR2(100 BYTE), ADDRESS VARCHAR2(200 BYTE) ); |
CREATE TABLE TBL_ORACLE
(
ID NUMBER
,Name VARCHAR2 (100)
,ADDRESS VARCHAR2 (200)
) ; |
分区（注释分区）
oracle配置参数中“#分区表唯一或主键约束”为“comment_partition”。
|
Oracle语法 |
迁移后语法 |
|---|---|
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); |
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语法 |
迁移后语法 |
|---|---|
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);
|
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语法 |
迁移后语法 |
|---|---|
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;
/
|
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语法 |
迁移后语法 |
|---|---|
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; / |
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不支持SEGMENT CREATION { IMMEDIATE | DEFERRED }，因此该语句在迁移后被注释掉，需要设置commentStorageParameter=true。
输入：TABLE，使用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
GaussDB不支持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)) ;
输出
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子句建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外联约束是关系属性子句或对象属性子句中的一部分。
如果参数foreignKeyHandler设置为true（默认值），工具将这些语句迁移为注释语句。
DSC支持内联和外联外键约束，如下所示。
输入：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 */
);
LONG数据类型
定义为LONG的列可存储变长字符数据，最多可包含2GB信息。MT支持表结构和PL/SQL中的LONG数据类型。
输入：在表结构中使用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;
/
TYPE
将“MDSYS.MBRCOORDLIST”替换为“CLOB”。
|
Oracle语法 |
迁移后语法 |
|---|---|
create table product_part ( partid VARCHAR2(24), mbrcoords MDSYS.MBRCOORDLIST ); |
CREATE TABLE product_part ( partid VARCHAR2(24), mbrcoords CLOB ); |
将“MDSYS.SDO_GEOMETRY”替换为“CLOB”。
|
Oracle语法 |
迁移后语法 |
|---|---|
create table product_part ( partid VARCHAR2(24), shape MDSYS.SDO_GEOMETRY ); |
CREATE TABLE product_part ( partid VARCHAR2(24), shape CLOB ); |
将“MDSYS.GEOMETRY”为“CLOB”。
|
Oracle语法 |
迁移后语法 |
|---|---|
create table product_part ( partid VARCHAR2(24), shape GEOMETRY ); |
CREATE TABLE product_part ( partid VARCHAR2(24), shape CLOB ); |
列
xmax、xmin、left、right、maxvalue为Gauss关键字，这些关键字应全字母大写并加英文双引号（""）。
|
Oracle语法 |
迁移后语法 |
|---|---|
create table product ( xmax VARCHAR2(20), xmin VARCHAR2(50), left VARCHAR2(50), right VARCHAR2(50), maxvalue VARCHAR2(50) ); |
CREATE TABLE product1 ( "XMAX" VARCHAR2(20), "XMIN" VARCHAR2(50), "LEFT" VARCHAR2(50), "RIGHT" VARCHAR2(50), "MAXVALUE" VARCHAR2(50) ); |
间隔分区
对于间隔分区，应该注释分区。
|
Oracle语法 |
迁移后语法 |
|---|---|
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'))); |
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')))*/; |
父主题： 模式对象
