使用MTK迁移Oracle到openGauss/MogDB

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "system",
      "port": 1521,
      "password": "oracle",
      "dbName": "mtk",
      "dsn": "",
      "charset": "ZHS16GBK",
      "pureDriver": true
    },
    "parameter": {
      "debugTest": false
    }
  }
}

SQL> create table tab_char(id number, col_char char(2));

Table created.

SQL> insert into tab_char values (1,'1');

1 row created.

SQL> select id,'"'||col_char||'"' ,col_char from tab_char;

    ID '"' C
---------- --- -
    1 "1 " 1

MogDB=#create table tab_char(id bigint, col_char char(2));
CREATE TABLE
MogDB=#insert into tab_char values (1,'1');
INSERT 0 1
MogDB=#select id,concat('"',col_char,'"') ,col_char from tab_char;
 id | concat | col_char
----+--------+----------
  1 | "1  "   | 1              >> 多了个空格
(1 row)

create index idx_test_01 on test_01(id,col1,col2);
alter table test_01 add constraint pk_test_01 primary key (id) using index idx_test_01;

-- mtk 正常转换
create unique index idx_test_01 on test_01(id,col1,col2);
alter table test_01 add constraint pk_test_01 primary key using index idx_test_01;
-- 以上结果是错误的

-- 正常
create unique index idx_test_01 on test_01(id,col1,col2);
create unique index idx_pk_test_01 on test_01(id);
alter table test_01 add constraint pk_test_01 primary key using index idx_pk_test_01;
-- 会比原库多一个唯一索引

SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS WHERE DB_LINK IS NULL;

SELECT col_name,dump(col_name,1016) FROM tab_ascii
COL2    DUMP_NAME
-----   ------------------------------------------------------------------------------------------
中国人   Typ=1 Len=9 CharacterSet=US7ASCII: e4,b8,ad,e5,9b,bd,e4,ba,ba

{
  "source": {
    "type": "oracle",
    "connect": {
      "clientCharset": "ASCII",
    }
  },
  "target": {
    "type": "opengauss",
    "connect": {
      "clientCharset": "UTF8"
    }
  }
}

SELECT SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY,
 ORDER_FLAG,
 CYCLE_FLAG,
 CACHE_SIZE,
 LAST_NUMBER,
 LAST_NUMBER + CACHE_SIZE AS STARTVAL
FROM
 DBA_SEQUENCES S
WHERE 1=1

SELECT SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY,
 ORDER_FLAG,
 CYCLE_FLAG,
 CACHE_SIZE,
 LAST_NUMBER,
 LAST_NUMBER + CACHE_SIZE AS STARTVAL
FROM
 DBA_SEQUENCES S
WHERE
 NOT EXISTS (
 SELECT
  1
 FROM
  DBA_TAB_IDENTITY_COLS C
 WHERE
  S.SEQUENCE_OWNER = C.OWNER
 AND S.SEQUENCE_NAME = C.SEQUENCE_NAME ) AND SEQUENCE_OWNER='ORA_MTK'

declare
  v_sequence_owner varchar2(100) := 'ORA_MTK'; ---大小写无关
  v_start_with number;
  v_ddl varchar2(4000);
  v_setval varchar2(4000);
begin
  for x in (select sequence_owner,
                   sequence_name,
                   min_value,
                   max_value,
                   increment_by,
                   case when cache_size= 0 then 1 else cache_size end cache_size,
                   cycle_flag,
                   last_number --Oracle 最后值是最后一次cache值
                   from dba_sequences where sequence_owner = upper(v_sequence_owner)) loop
  -- 通过xx.nextval查询可以保证在单节点下是最后值,无法保证在rac节点下是最后值. 因为是内存数据
  execute immediate 'select ' || x.sequence_owner || '.' || x.sequence_name ||'.nextval from dual' into v_start_with;
  --  v_ddl:= case when x.max_value>9223372036854775807 then 'create large sequence ' else 'create sequence ' end
  --         || lower(x.sequence_owner) || '.' || lower(x.sequence_name) || ' minvalue ' || x.min_value || ' maxvalue '
  --         || x.max_value || ' increment by ' || x.increment_by || ' start with ' || v_start_with || ' cache '
  --         || x.cache_size || case when x.cycle_flag='N' then ' nocycle' else ' cycle' end ||';' ;
  -- dbms_output.put_line(v_ddl);
 v_setval :='SELECT setval('||''''||x.sequence_owner || '.' || x.sequence_name||''''||', '||v_start_with||');';
 dbms_output.put_line(v_setval);
 end loop;
end;

SELECT
 o.object_type,
 s.owner,
 s.name,
 s.line,
 s.text,
 t.typecode,
 o.timestamp,
 h.lvl
FROM
 DBA_SOURCE s,
 DBA_TYPES t,
 DBA_OBJECTS o,
 (
 SELECT
  NAME,
  MAX(LEVEL) lvl
 FROM
  (
  SELECT
   *
  FROM
   DBA_DEPENDENCIES
  WHERE
   TYPE = 'TYPE'
   AND owner = 'ORA_MTK')
 CONNECT BY
  nocycle referenced_name = PRIOR name
 GROUP BY
  name) h
WHERE
 s.type = 'TYPE'
 AND o.object_type = 'TYPE'
 AND s.owner = 'ORA_MTK'
 AND h.name = s.name
 AND s.owner = t.owner
 AND s.name = t.type_name
 AND s.owner = o.owner
 AND s.name = o.object_name
 AND o.status = 'VALID'
UNION ALL
SELECT
 o.object_type,
 s.owner,
 s.name,
 s.line,
 s.text,
 t.typecode,
 o.timestamp,
 h.lvl
FROM
 DBA_SOURCE s,
 DBA_TYPES t,
 DBA_OBJECTS o,
 (
 SELECT
  NAME,
  MAX(LEVEL) lvl
 FROM
  (
  SELECT
   *
  FROM
   DBA_DEPENDENCIES
  WHERE
   TYPE = 'TYPE'
   AND owner = 'ORA_MTK')
 CONNECT BY
  nocycle referenced_name = PRIOR name
 GROUP BY
  name) h
WHERE
 s.type = 'TYPE BODY'
 AND o.object_type = 'TYPE BODY'
 AND s.owner = 'ORA_MTK'
 AND h.name = s.name
 AND s.owner = t.owner
 AND s.name = t.type_name
 AND s.owner = o.owner
 AND s.name = o.object_name
 AND o.status = 'VALID'
ORDER BY
 lvl,
 OWNER,
 NAME,
 object_type,
 LINE

SELECT
 Q.OWNER,
 NAME,
 QUEUE_TYPE,
 Q.QUEUE_TABLE,
 QT.OBJECT_TYPE
FROM
 DBA_QUEUES Q,
 DBA_QUEUE_TABLES QT
WHERE
 Q.QUEUE_TABLE = QT.QUEUE_TABLE
 AND Q.OWNER = QT.OWNER AND

SELECT A.OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.CLUSTER_NAME,A.IOT_NAME,A.LOGGING,A.PARTITIONED,A.IOT_TYPE,
  A.TEMPORARY,
  A.SECONDARY,
  A.NESTED,
  A.COMPRESSION,
  A.COMPRESS_FOR,
  B.COMMENTS,
  A.DURATION ,
  A.NUM_ROWS ,
  A.BLOCKS,
  A.AVG_ROW_LEN,
  A.TABLE_TYPE,
  A.TABLE_TYPE_OWNER
 FROM
 DBA_ALL_TABLES A,
 DBA_OBJECTS O,
 DBA_TAB_COMMENTS B
 WHERE NOT EXISTS (SELECT 1 FROM DBA_SNAPSHOTS S WHERE S.OWNER = A.OWNER AND S.TABLE_NAME=A.TABLE_NAME)
 AND A.OWNER=B.OWNER(+)
 AND A.TABLE_NAME=B.TABLE_NAME(+)
 AND A.OWNER=O.OWNER
 AND A.TABLE_NAME=O.OBJECT_NAME
 AND O.OBJECT_TYPE='TABLE'

SELECT
 /*+ RULE */
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 COLUMN_ID,
 DATA_DEFAULT,
 DATA_TYPE,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 CHAR_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 CHAR_USED,
 HIDDEN_COLUMN,
 VIRTUAL_COLUMN,
 NULLABLE,
 NULL COMMENTS,
 NULL AS IDENTITY_COLUMN
FROM
 DBA_TAB_COLS C
WHERE 1=1 AND HIDDEN_COLUMN='NO'  AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2') ORDER BY c.OWNER,c.TABLE_NAME,c.COLUMN_ID

SELECT
 /*+ RULE */
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 COLUMN_ID,
 DATA_DEFAULT,
 DATA_TYPE,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 CHAR_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 CHAR_USED,
 HIDDEN_COLUMN,
 VIRTUAL_COLUMN,
 NULLABLE,
 NULL COMMENTS,
 C.IDENTITY_COLUMN -- 12.1
FROM
 DBA_TAB_COLS C
WHERE 1=1 AND HIDDEN_COLUMN='NO'  AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2') ORDER BY c.OWNER,c.TABLE_NAME,c.COLUMN_ID

SELECT c.owner, c.table_name, c.column_name, comments FROM DBA_COL_COMMENTS c WHERE c.comments IS NOT null

SELECT
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 C.SEQUENCE_NAME,
 C.GENERATION_TYPE,
 S.MIN_VALUE,
 S.MAX_VALUE,
 S.INCREMENT_BY,
 S.ORDER_FLAG,
 S.CYCLE_FLAG,
 S.CACHE_SIZE,
 S.LAST_NUMBER,
 S.LAST_NUMBER + S.CACHE_SIZE AS STARTVAL
FROM
 DBA_TAB_IDENTITY_COLS C,
 DBA_SEQUENCES S
WHERE
 S.SEQUENCE_OWNER = C.OWNER
 AND S.SEQUENCE_NAME = C.SEQUENCE_NAME

SELECT  C.OWNER,
 C.CONSTRAINT_NAME,
 C.CONSTRAINT_TYPE,
 C.TABLE_NAME,
 C.SEARCH_CONDITION,
 C.R_OWNER,
 C.R_CONSTRAINT_NAME,
 C.DELETE_RULE,
 C.STATUS,
 C.DEFERRABLE,
 C.DEFERRED,
 C.VALIDATED,
 C.INDEX_OWNER,
 C.INDEX_NAME
FROM
 DBA_CONSTRAINTS C
WHERE
 1 = 1 AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2')
UNION ALL
 SELECT
 R.OWNER,
 R.CONSTRAINT_NAME,
 R.CONSTRAINT_TYPE,
 R.TABLE_NAME,
 NULL AS SEARCH_CONDITION,
 R.R_OWNER,
 R.R_CONSTRAINT_NAME,
 R.DELETE_RULE,
 R.STATUS,
 R.DEFERRABLE,
 R.DEFERRED,
 R.VALIDATED,
 R.INDEX_OWNER,
 R.INDEX_NAME
FROM
 DBA_CONSTRAINTS C
 LEFT JOIN DBA_CONSTRAINTS R ON C.R_OWNER = R.OWNER AND C.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME
WHERE
 1 = 1
 AND C.CONSTRAINT_TYPE='R'
 AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2')

SELECT
 COLS.OWNER,
 COLS.CONSTRAINT_NAME,
 COLS.TABLE_NAME,
 COLS.COLUMN_NAME,
 COLS.POSITION ,
 TC.NULLABLE
FROM
 DBA_CONS_COLUMNS COLS ,
 DBA_TAB_COLS TC
WHERE
 COLS.TABLE_NAME = TC.TABLE_NAME
 AND COLS.OWNER = TC.OWNER
 AND COLS.COLUMN_NAME = TC.COLUMN_NAME
 AND UPPER(COLS.OWNER) = 'ORA_MTK' AND (UPPER(COLS.TABLE_NAME) = 'T3' OR UPPER(COLS.TABLE_NAME) = 'T2')
UNION
SELECT DISTINCT COLS.OWNER,
 COLS.CONSTRAINT_NAME,
 COLS.TABLE_NAME,
 COLS.COLUMN_NAME,
 COLS.POSITION ,
 TC.NULLABLE
FROM
 DBA_CONSTRAINTS CONS,
 DBA_CONSTRAINTS CONS_R,
 DBA_CONS_COLUMNS COLS,
 DBA_TAB_COLS TC
WHERE
 1 = 1
 AND CONS.R_OWNER = CONS_R.OWNER
 AND CONS.R_CONSTRAINT_NAME = CONS_R.CONSTRAINT_NAME
 AND CONS_R.TABLE_NAME = COLS.TABLE_NAME
 AND CONS_R.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
 AND CONS_R.OWNER = COLS.OWNER
 AND CONS_R.TABLE_NAME = TC.TABLE_NAME
 AND CONS_R.OWNER = TC.OWNER
 AND COLS.OWNER = TC.OWNER
 AND COLS.TABLE_NAME = TC.TABLE_NAME
 AND COLS.COLUMN_NAME = TC.COLUMN_NAME
 AND CONS.CONSTRAINT_TYPE ='R'
 AND UPPER(COLS.OWNER) = 'ORA_MTK' AND (UPPER(COLS.TABLE_NAME) = 'T3' OR UPPER(COLS.TABLE_NAME) = 'T2')
 ORDER BY OWNER,TABLE_NAME,CONSTRAINT_NAME,POSITION

SELECT
 S.INDEX_NAME,
 S.TABLE_NAME,
 S.UNIQUENESS,
 S.INDEX_TYPE,
 S.TABLE_OWNER,
 S.PARTITIONED,
 S.TABLESPACE_NAME,
 Q.QUEUE_TABLE,
 S.JOIN_INDEX
FROM
 DBA_INDEXES S
LEFT OUTER JOIN DBA_QUEUE_TABLES Q ON
 Q.QUEUE_TABLE = S.TABLE_NAME
 AND Q.OWNER = S.TABLE_OWNER
WHERE EXISTS (
 SELECT
  AC.INDEX_NAME
 FROM
  DBA_CONSTRAINTS AC
 WHERE
   AC.CONSTRAINT_TYPE IN ('P', 'U')
  AND AC.TABLE_NAME = S.TABLE_NAME
  AND AC.OWNER = S.TABLE_OWNER
  AND AC.INDEX_NAME = S.INDEX_NAME
 )
 AND S.TEMPORARY = 'N'
 AND (FUNCIDX_STATUS IS NULL
 OR FUNCIDX_STATUS != 'DISABLED')
 AND UPPER(S.TABLE_OWNER) = 'ORA_MTK' AND UPPER(S.TABLE_NAME) = 'PROMOTIONS_VAR3'

SELECT IC.INDEX_OWNER,IC.INDEX_NAME,IC.COLUMN_NAME,IE.COLUMN_EXPRESSION,IC.COLUMN_POSITION,IC.TABLE_NAME,IC.TABLE_OWNER,IC.DESCEND FROM DBA_IND_COLUMNS IC, DBA_IND_EXPRESSIONS IE WHERE IC.INDEX_OWNER = IE.INDEX_OWNER(+) AND IC.INDEX_NAME = IE.INDEX_NAME(+) AND IC.COLUMN_POSITION=IE.COLUMN_POSITION(+)
AND UPPER(IC.TABLE_OWNER) = 'ORA_MTK' AND UPPER(IC.TABLE_NAME) = 'PROMOTIONS_VAR3'
ORDER BY IC.TABLE_OWNER,IC.TABLE_NAME,IC.INDEX_NAME,IC.COLUMN_POSITION

SELECT
 S.INDEX_NAME,
 S.TABLE_NAME,
 S.UNIQUENESS,
 S.INDEX_TYPE,
 S.TABLE_OWNER,
 S.PARTITIONED,
 S.TABLESPACE_NAME,
 Q.QUEUE_TABLE,
 S.JOIN_INDEX
FROM
 DBA_INDEXES S
LEFT OUTER JOIN DBA_QUEUE_TABLES Q ON
 Q.QUEUE_TABLE = S.TABLE_NAME
 AND Q.OWNER = S.TABLE_OWNER
WHERE NOT EXISTS (
 SELECT
  AC.INDEX_NAME
 FROM
  DBA_CONSTRAINTS AC
 WHERE
   AC.CONSTRAINT_TYPE IN ('P', 'U')
  AND AC.TABLE_NAME = S.TABLE_NAME
  AND AC.OWNER = S.TABLE_OWNER
  AND AC.INDEX_NAME = S.INDEX_NAME
 )
 AND S.TEMPORARY = 'N'
 AND (FUNCIDX_STATUS IS NULL
 OR FUNCIDX_STATUS != 'DISABLED')
 AND UPPER(S.TABLE_OWNER) = 'ORA_MTK' AND UPPER(S.TABLE_NAME) = 'PROMOTIONS_VAR3'

SELECT IC.INDEX_OWNER,IC.INDEX_NAME,IC.COLUMN_NAME,IE.COLUMN_EXPRESSION,IC.COLUMN_POSITION,IC.TABLE_NAME,IC.TABLE_OWNER,IC.DESCEND FROM DBA_IND_COLUMNS IC, DBA_IND_EXPRESSIONS IE WHERE IC.INDEX_OWNER = IE.INDEX_OWNER(+) AND IC.INDEX_NAME = IE.INDEX_NAME(+) AND IC.COLUMN_POSITION=IE.COLUMN_POSITION(+)
AND UPPER(IC.TABLE_OWNER) = 'ORA_MTK' AND UPPER(IC.TABLE_NAME) = 'PROMOTIONS_VAR3'
ORDER BY IC.TABLE_OWNER,IC.TABLE_NAME,IC.INDEX_NAME,IC.COLUMN_POSITION

SELECT OWNER, DB_LINK, USERNAME,HOST FROM DBA_DB_LINKS WHERE 1=1

SELECT V.OWNER,V.VIEW_NAME,V.TEXT,V.TEXT_VC ,O.STATUS
FROM DBA_VIEWS V,DBA_OBJECTS O
WHERE V.OWNER = O.OWNER AND V.VIEW_NAME = O.OBJECT_NAME

SELECT
 MV.OWNER,
 MV.MVIEW_NAME,
 MV.BUILD_MODE,
 MV.REFRESH_METHOD,
 MV.REFRESH_MODE,
 MV.REWRITE_ENABLED,
 MV.QUERY,
 O.STATUS,
 R.INTERVAL
FROM
 DBA_MVIEWS MV,
 DBA_OBJECTS O,
 DBA_REFRESH R
WHERE
 O.OBJECT_TYPE = 'MATERIALIZED VIEW'
 AND MV.OWNER = O.OWNER
 AND MV.MVIEW_NAME = O.OBJECT_NAME
 AND O.STATUS = 'VALID'
 AND MV.OWNER = R.ROWNER(+)
 AND MV.MVIEW_NAME = R.RNAME(+) AND UPPER(MV.OWNER) = 'ORA_MTK' AND (UPPER(MV.MVIEW_NAME) = 'T3' OR UPPER(MV.MVIEW_NAME) = 'T2')

SELECT
 S.NAME,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND S.TYPE = 'FUNCTION'
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

SELECT
 S.NAME,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND S.TYPE = 'PROCEDURE'
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

SELECT
 S.NAME,
 S.TYPE,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND (S.TYPE = 'PACKAGE' OR S.TYPE = 'PACKAGE BODY')
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

SELECT
 T.TRIGGER_NAME,
 T.OWNER,
 T.TABLE_OWNER,
 T.TRIGGER_TYPE,
 T.TRIGGERING_EVENT,
 T.TABLE_NAME,
 T.TRIGGER_BODY,
 T.WHEN_CLAUSE,
 T.DESCRIPTION,
 T.STATUS,
 T.BASE_OBJECT_TYPE,
 T.ACTION_TYPE
FROM
 DBA_TRIGGERS T,
 DBA_OBJECTS O
WHERE
 O.OBJECT_NAME = T.TRIGGER_NAME
 AND O.OWNER = T.TABLE_OWNER
 -- AND o.status = 'VALID'
 AND O.OBJECT_TYPE = 'TRIGGER'

SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS WHERE DB_LINK IS NULL