Home PageApsaraDB for OceanBaseUser GuideOMS User GuideAppendixFeaturesHidden column mechanism of the data transmission service
Search for Help Content

Hidden column mechanism of the data transmission service

Updated at: 2025-02-26 01:40
Community

When you perform schema migration or synchronize an incremental DDL statement CREATE TABLE, the data transmission service will provide the corresponding mechanism based on the actual situation of the data migration task.

Background

You can manually drop the hidden columns and unique indexes based on the type of the data migration task as guided by this topic before you advance to the forward switchover phase of a data migration task.

If you have not manually dropped the hidden columns and unique indexes, the data transmission service will automatically drop the hidden columns and unique indexes based on the type of the data migration task in the forward switchover phase.

  • When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, the data transmission service will add hidden columns to all tables at the target database, and create unique indexes based on the hidden columns if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options page. These columns and indexes will be automatically dropped in the forward switchover phase. For more information about how to create a task, see Migrate data from an Oracle database to an Oracle tenant of OceanBase Database.

    The data transmission service will automatically add 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
);

  • For data migration tasks listed in the following table, to ensure the migration of tables without a unique key, the data transmission service will add hidden columns for corresponding tables at the target and create a unique index based on the hidden columns. These hidden columns and indexes will be automatically dropped in the forward switchover phase.

    Source

    Target

    Reference

    Source

    Target

    Reference

    MySQL tenant of OceanBase Database

    MySQL tenant of OceanBase Database

    Migrate data between OceanBase databases

    Synchronize data between tenants of the same type in OceanBase Database

    Oracle tenant of OceanBase Database

    Oracle tenant of OceanBase Database

    Oracle tenant of OceanBase Database

    Oracle database

    Migrate data from an Oracle tenant of OceanBase Database to an Oracle database

    MySQL tenant of OceanBase Database

    Oracle tenant of OceanBase Database

    Synchronize data between tenants of different types in OceanBase Database

    Oracle tenant of OceanBase Database

    MySQL tenant of OceanBase Database

    • When you migrate or synchronize data between MySQL tenants of OceanBase Database, the data transmission service will automatically add hidden columns, hidden column comments, and unique indexes to the CREATE TABLE statement if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options or Synchronization Options page. 

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

    • When you migrate or synchronize data between Oracle tenants of OceanBase Database, the data transmission service will automatically add hidden columns to the CREATE TABLE statement if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options or Synchronization Options page. 

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

    • When you migrate data from an Oracle tenant of OceanBase Database to an Oracle database, the data transmission service will automatically add hidden columns to the CREATE TABLE statement if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options page. 

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

Usage notes

  • "{partition_col_0}" or `{partition_col_0}` indicates a table partitioning key column. Multiple such fields may exist. Separate the fields with commas (,), such as "{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 tenant of OceanBase Database

When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, the data transmission service will add hidden columns and create unique indexes for tables with primary keys.

For tables without primary keys or non-null unique keys, the data transmission service will automatically add hidden columns and unique indexes to the tables if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options page during migration task configuration.

image

  • 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

    // Partitioning key columns are 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");

Data migration or synchronization between MySQL tenants of OceanBase Database

When you migrate or synchronize data between MySQL tenants of OceanBase Database, the data transmission service will automatically add hidden columns and unique indexes to tables without a primary key or non-null unique key if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options or Synchronization Options page.

image

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

    // Partitioning key columns are available.
CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
// No partitioning key column is available.
CREATE UNIQUE KEY `UK_{table}_OBPK_INCRMT` (`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 or synchronization between Oracle tenants of OceanBase Database

When you migrate or synchronize data between Oracle tenants of OceanBase Database, the data transmission service will automatically add hidden columns and unique indexes to tables without a primary key or non-null unique key if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options or Synchronization Options page.

image

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

    // Partitioning key columns are 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 tenant of OceanBase Database to an Oracle database

When you migrate data from an Oracle tenant of OceanBase Database to an Oracle database, the data transmission service will automatically add hidden columns and unique indexes to tables without a primary key or non-null unique key if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Migration Options page.

image

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 Database 12c
ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
// Oracle Database 12c and later
ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

  • Add unique indexes

    // Partitioning key columns are 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 synchronization from a MySQL tenant to an Oracle tenant in OceanBase Database

When you synchronize data from a MySQL tenant to an Oracle tenant in OceanBase Database, the data transmission service will automatically add hidden columns and unique indexes to tables without a primary key or non-null unique key if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Synchronization Options page.

image

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 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 hidden columns

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

  • Add unique indexes

    // Partitioning key columns are 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 synchronization from an Oracle tenant to a MySQL tenant in OceanBase Database

When you synchronize data from an Oracle tenant to a MySQL tenant in OceanBase Database, the data transmission service will automatically add hidden columns and unique indexes to tables without a primary key or non-null unique key if you have selected Yes for Add hidden columns for tables without non-null unique keys in the Advanced Options section on the Synchronization Options page.

image

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 COLUMN `oms_pk_incrmt` BIGINT;

  • Add unique indexes

    // Partitioning key columns are available.
CREATE UNIQUE KEY `uk_{table}_obpk_incrmt` (`{partition_col_0}`, `{partition_col_1}`, `oms_pk_incrmt`) LOCAL;
// No partitioning key column is available.
CREATE UNIQUE KEY `uk_{table}_obpk_incrmt` (`oms_pk_incrmt`);

  • Drop unique indexes

    DROP INDEX `uk_{table}_obpk_incrmt` ON `{schema}`.`{table}`;

  • Drop hidden columns

    ALTER TABLE `{schema}`.`{table}` DROP COLUMN `oms_pk_incrmt`;
Previous: Wildcard patterns supported for matching rulesNext: Instructions on schema migration
  • On this page （0, O）
  • Background
  • Usage notes
  • Data migration from an Oracle database to an Oracle tenant of OceanBase Database
  • Data migration or synchronization between MySQL tenants of OceanBase Database
  • Data migration or synchronization between Oracle tenants of OceanBase Database
  • Data migration from an Oracle tenant of OceanBase Database to an Oracle database
  • Data synchronization from a MySQL tenant to an Oracle tenant in OceanBase Database
  • Data synchronization from an Oracle tenant to a MySQL tenant in OceanBase Database
Feedback