Oracle中部分不兼容对象迁移到OceanBase的三种处理方式

CREATE TABLE t_lob(
    c_ID NUMBER,
    c_clob CLOB,
    c_blob BLOB
);

CREATE OR REPLACE PROCEDURE insert_random_lob_data AS
BEGIN
  DECLARE
    l_random_string VARCHAR两(10000);
    l_random_blob BLOB;
  BEGIN
    FOR i IN 1..100 LOOP
      l_random_string := dbms_random.string('U', dbms_random.value(1, 10000));
      dbms_lob.createtemporary(l_random_blob, TRUE);
      dbms_lob.writeappend(l_random_blob, LENGTH(l_random_string), utl_raw.cast_to_raw(l_random_string));

      INSERT INTO t_lob(c_ID, c_clob, c_blob)
      VALUES(i, l_random_string, l_random_blob);

      dbms_lob.freetemporary(l_random_blob);
    END LOOP;
    COMMIT;
  END;
END;
/

SELECT MAX(DBMS_LOB.GETLENGTH(C_CLOB)) AS LONGEST_CLOB,
       MAX(DBMS_LOB.GETLENGTH(C_BLOB)) AS LONGEST_BLOB
  FROM T_LOB;

SELECT COL.OWNER,
       COL.TABLE_NAME,
       COL.COLUMN_NAME,
       COL.DATA_TYPE,
       COL.AVG_COL_LEN,
       COL.CHAR_LENGTH,
       TAB.NUM_ROWS
  FROM DBA_TABLES TAB, DBA_TAB_COLUMNS COL
 WHERE TAB.OWNER = COL.OWNER
   AND TAB.TABLE_NAME = COL.TABLE_NAME
   AND COL.DATA_TYPE IN ('CLOB', 'BLOB')
   AND COL.OWNER NOT IN ('SYS', 'SYSTEM')
   AND COL.OWNER IN
       (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN')
   AND COL.TABLE_NAME NOT LIKE 'BIN%';

-- [WARN] [DISCARD] CONSTRAINT "PK_T_PARTKEY_IS_PK" PRIMARY KEY ("CRT_DTTM") DISABLE NOVALIDATE -> [NULL]
-- [WARN] [DISCARD] CHECK ("ACT_ID" IS NOT NULL) DISABLE NOVALIDATE -> [NULL]
CREATE TABLE "T_PARTKEY_IS_PK" (
    "ACT_ID" NUMBER(10,0),
    "SRT_ID" NUMBER(10,0),
    "SRT_ORIGNAL_ID" NUMBER(10,0),
    "CRT_DTTM" DATE,
    "LASTUPT_DTTM" DATE
)

-- 脚工将T_PARTKEY_IS_PK表的约束皆disable
ALTER TABLE ZHENXING.T_PARTKEY_IS_PK DISABLE NOVALIDATE CONSTRAINT PK_T_PARTKEY_IS_PK;
ALTER TABLE ZHENXING.T_PARTKEY_IS_PK DISABLE CONSTRAINT SYS_C0011109;

SELECT OWNER,
       TABLE_NAME,
       CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       INDEX_NAME,
       STATUS
  FROM DBA_CONSTRAINTS
 WHERE OWNER = 'ZHENXING'
   AND TABLE_NAME = 'T_PARTKEY_IS_PK';

-- OMS 迁徙表规划时记载的WARN疑息，示意自发分区属性因为没有兼容会自觉DISCARD舍弃
[WARN] [DISCARD]  INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) -> [NULL]

-- 建立基于地的主动分区表
SQL> create table interval_sales (
    prod_id number(6),
    time_id date)
    partition by range (time_id)
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    (partition p1 values less than (to_date('两015-01-01','yyyy-妹妹-dd')));

-- 盘问当前分区，默许天生了1个界说孬的分区
SQL> SELECT TABLE_NAME, PARTITION_NAME
  FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME = 'INTERVAL_SALES';

TABLE_NAME                       PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_SALES                       P1

-- 拔出数据（没有正在默许分区内）
SQL> INSERT INTO INTERVAL_SALES VALUES(001, TO_DATE('两015-0两-01', 'yyyy-妹妹-dd'));

-- 主动天生了新分区
TABLE_NAME                       PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_SALES                       P1
INTERVAL_SALES                       SYS_P两两1

-- 独自查望该分区数据（验证数据简直具有新分区）
SQL> SELECT * FROM INTERVAL_SALES PARTITION(SYS_P二两1);

   PROD_ID TIME_ID
---------- ---------
         1 01-FEB-15

/*
PARTITION_COUNT: Number of partitions in the table. For interval partitioned tables, the value of this column is always 1048575.
*/
SELECT T1.OWNER,
       T1.TABLE_NAME,
       T1.INTERVAL,
       T1.PARTITIONING_TYPE,
       T1.PARTITION_COUNT,
       T1.SUBPARTITIONING_TYPE      AS SUB_TYPE,
       T1.SUBPARTITIONING_KEY_COUNT SUB_COUNT,
       T1.STATUS
  FROM DBA_PART_TABLES T1
 WHERE 1 = 1
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND (INTERVAL IS NOT NULL OR PARTITION_COUNT = 1048575);