Hidden column mechanisms

During schema migration or incremental synchronization of the DDL statement CREATE TABLE, OceanBase Migration Service (OMS) provides corresponding mechanisms based on the actual situation of the data migration task.

Background information

If you do not advance to the forward switchover step in a data migration task, you must manually drop the hidden columns and unique indexes based on the type of the data migration task as guided by this topic.

When you advance to the forward switchover step in a data migration task, OMS will automatically drop the hidden columns and unique indexes based on the type of the data migration task.

• When you migrate data from an Oracle database to an Oracle-compatible tenant of OceanBase Database, OMS will add hidden columns to all tables in the target database, and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped during the forward switchover step. For more information about how to create a task, see Migrate data from an Oracle database to an Oracle-compatible tenant of OceanBase Database.

  OMS automatically adds hidden columns to the CREATE TABLE statement. Here is an example:

  CREATE TABLE "table1" (
   "S1" NVARCHAR2(2) NOT NULL,
   "S2" NVARCHAR2(20) DEFAULT 'Default value field',
   "OMS_OBJECT_NUMBER" NUMBER INVISIBLE,
   "OMS_RELATIVE_FNO" NUMBER INVISIBLE,
   "OMS_BLOCK_NUMBER" NUMBER INVISIBLE,
   "OMS_ROW_NUMBER" NUMBER INVISIBLE
   );
  

• To migrate tables without a unique key for data migration tasks of the following types, OMS adds hidden columns to corresponding tables in the target database and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped in the forward switchover step.

  Source	Target	Reference
  MySQL-compatible tenant of OceanBase Database	MySQL-compatible tenant of OceanBase Database	Migrate data within OceanBase Database
  Oracle-compatible tenant of OceanBase Database	Oracle-compatible tenant of OceanBase Database	Migrate data within OceanBase Database
  Oracle-compatible tenant of OceanBase Database	Oracle database	Migrate data from an Oracle-compatible tenant of OceanBase Database to an Oracle database
  Oracle database	MySQL-compatible tenant of OceanBase Database	Migrate data from an Oracle database to a MySQL-compatible tenant of OceanBase Database

  • For data migration between MySQL-compatible tenants of OceanBase Database, OMS will automatically add hidden columns, hidden column comments, and unique indexes to the CREATE TABLE statement.

    // Create a hidden column and a unique index for a table without a partitioning key column.
    create table `table2` (
        `c1` binary(1),
        `OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
        UNIQUE KEY `UK_table2_OBPK_INCRMT` (`OMS_PK_INCRMT`)
    );
    // Create a hidden column and a unique index for a table with a partitioning key column.
    create table `partlistcolumns_key_nopri` (
        `col1` int(11),
        `col2` decimal(10,2),
        `col3` decimal(10,2),
        `col4` bit(1),
        `col5` tinyint(4),
        `col6` smallint(6),
        `OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
        UNIQUE KEY `UK_partlistcolumns_key_nopri_OBPK_INCRMT` (`col1`,`col6`, `OMS_PK_INCRMT`) LOCAL
    )
    default charset=utf8mb4
    default collate=utf8mb4_general_ci
    PARTITION BY LIST COLUMNS (col1)
    SUBPARTITION BY KEY (col6)
    subpartition template (
        SUBPARTITION `p0`,
        SUBPARTITION `p1`,
        SUBPARTITION `p2`,
        SUBPARTITION `p3`
    )
    (
        PARTITION `p1` VALUES IN (1),
        PARTITION `p2` VALUES IN (2),
        PARTITION `p3` VALUES IN (3),
        PARTITION `p4` VALUES IN (4)
    );
    

  • For data migration between Oracle-compatible tenants of OceanBase Database, OMS will automatically add hidden columns to the CREATE TABLE statement.

    CREATE TABLE "table4" (
        "V1" CHAR(20 BYTE) DEFAULT 'test',
        "OMS_PK_INCRMT" NUMBER INVISIBLE
    );
    

  • For data migration from an Oracle-compatible tenant of OceanBase Database to an Oracle database, OMS will automatically add hidden columns to the CREATE TABLE statement.

    // Versions earlier than Oracle 12c
    CREATE TABLE "table3" (
        "id" NUMBER,
        "status" VARCHAR2(1 BYTE),
        "OMS_PK_INCRMT" NUMBER
    );
    // Oracle 12c and later
    CREATE TABLE "table3" (
        "id" NUMBER,
        "status" VARCHAR2(1 BYTE),
        "OMS_PK_INCRMT" NUMBER INVISIBLE
    );
    

  • For data migration from an Oracle database to a MySQL-compatible tenant of OceanBase Database, OMS will automatically add hidden columns and hidden column comments to the CREATE TABLE statement.

    CREATE TABLE "table5" (
        "V1" CHAR(20) BINARY DEFAULT 'test',
        "OMS_OBJECT_NUMBER" BIGINT COMMENT 'Reserved for data migration tasks of OMS',
        "OMS_RELATIVE_FNO" BIGINT COMMENT 'Reserved for data migration tasks of OMS',
        "OMS_BLOCK_NUMBER" BIGINT COMMENT 'Reserved for data migration tasks of OMS',
        "OMS_ROW_NUMBER" BIGINT COMMENT 'Reserved for data migration tasks of OMS',
    UNIQUE KEY "UK_{table}_OMS_ROWID" (
        "OMS_OBJECT_NUMBER", 
        "OMS_RELATIVE_FNO", 
        "OMS_BLOCK_NUMBER", 
        "OMS_ROW_NUMBER"
        )
    );
    

Prerequisites

• "{partition_col_0}" or `{partition_col_0}` indicates a table partitioning key column. Multiple such fields may exist. Separate the fields with commas (,), for example, "{partition_col_0}", "{partition_col_1}","{partition_col_2}" or `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`.

• {schema}, {table}, {partition_col_0}, and {partition_col_1} are respectively placeholders for the database name, table name, and partitioning key column names.

Data migration from an Oracle database to an Oracle-compatible tenant of OceanBase Database

When you migrate data from an Oracle database to an Oracle-compatible tenant of OceanBase Database, OMS will add hidden columns and create unique indexes for the tables, regardless of whether they have primary keys.

• Add hidden columns

  ALTER TABLE "{schema}"."{table}" ADD "OMS_OBJECT_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_RELATIVE_FNO" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_BLOCK_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_ROW_NUMBER" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER", "{partition_col_0}", "{partition_col_1}") LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");
  

• Drop unique indexes

  DROP INDEX "{schema}"."UK_{table}_OMS_ROWID";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");
  

Migrate data between MySQL-compatible tenants of OceanBase Database

When you migrate data between MySQL-compatible tenants of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA = {schema} AND TABLE_NAME = {table} GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING count(1) = count(IF(upper(nullable) != 'YES' and NON_UNIQUE = 0, 1, NULL));

• Add columns

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_PK_INCRMT` BIGINT;
  

• Add unique indexes

  // A partitioning key column is available.
  CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`OMS_PK_INCRMT`);
  

• Drop unique indexes

  DROP INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}`;
  

• Drop columns

  ALTER TABLE `{schema}`.`{table}` DROP COLUMN `OMS_PK_INCRMT`;
  

Data migration between Oracle-compatible tenants of OceanBase Database

When you migrate data between Oracle-compatible tenants of OceanBase Database, OMS will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• Add hidden columns

  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");
  

• Drop unique indexes

  DROP INDEX "{schema}"."UK_{table}_OBPK_INCRMT";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";
  

Data migration from an Oracle-compatible tenant of OceanBase Database to an Oracle database

When you migrate data from an Oracle-compatible tenant of OceanBase Database to an Oracle database, OMS will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• Add hidden columns

  // Versions earlier than Oracle 12c
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
  // Oracle 12c and later
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT");
  

• Drop unique indexes

  DROP INDEX "{schema}"."{table}";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";
  

Data migration from an Oracle database to a MySQL-compatible tenant of OceanBase Database

When you migrate data from an Oracle database to a MySQL-compatible tenant of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

select 1 from ALL_CONS_COLUMNS DCC join ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER join ALL_TAB_COLUMNS DTC on DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME where DCC.owner = {schema} AND DCC.TABLE_NAME = {table} and DC.constraint_type in ('U', 'P') group by DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME having count(*) = count(CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END);

• Add columns

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_OBJECT_NUMBER` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_RELATIVE_FNO` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_BLOCK_NUMBER` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_ROW_NUMBER` BIGINT;
  

• Add unique indexes

  // A partitioning key column is available.
  CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`, `{partition_col_0}`, `{partition_col_1}`) LOCAL;
  // No partitioning key column is available.
  CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`);
  

• Drop unique indexes

  DROP INDEX UK_{table}_OMS_ROWID ON {table};
  

• Drop columns

  ALTER TABLE `{schema}`.`{table}`  DROP COLUMN `OMS_OBJECT_NUMBER`, DROP COLUMN `OMS_RELATIVE_FNO`, DROP COLUMN `OMS_BLOCK_NUMBER`, DROP COLUMN `OMS_ROW_NUMBER`;