SQL statements for querying table objects

When you create a data migration or synchronization task, Migrations automatically filters out unsupported tables after you specify the migration or synchronization objects. Therefore, the number of identified tables may be inconsistent with the actual number of tables that need to be migrated or synchronized. This topic describes the SQL statements for you to query table objects.

Query the basic information about tables

The SQL statements for querying the basic information about tables in data sources of different types are described as follows:

• A MySQL data source

  SELECT
      NULL TABLE_CATALOG,
      TABLE_SCHEMA,
      TABLE_NAME,
      TABLE_ROWS,
      TABLE_COLLATION,
      ENGINE
      FROM
      information_schema.tables
      WHERE
      TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA NOT IN(
          'mysql',
          'information_schema',
          'performance_schema'
      )
      AND TABLE_SCHEMA IN ()
      AND TABLE_NAME IN ();
  

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      NULL TABLE_CATALOG,
      TABLE_SCHEMA,
      TABLE_NAME,
      TABLE_ROWS,
      TABLE_COLLATION,
      ENGINE
      FROM
      information_schema.tables
      WHERE
      TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA NOT IN(
          'mysql',
          'information_schema',
          'performance_schema'
      )
      AND BINARY TABLE_SCHEMA IN ()
      AND BINARY TABLE_NAME IN ();
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      NULL,
      A.OWNER,
      A.TABLE_NAME,
      A.NUM_ROWS,
      A.PARTITIONED,
      A.IOT_TYPE
  FROM
      ALL_TABLES A
  WHERE
      A.TABLE_NAME IN (
          SELECT
              OBJECT_NAME
          FROM
              ALL_OBJECTS
          WHERE
              UPPER(OBJECT_TYPE) = 'TABLE'
      )
      AND A.OWNER NOT IN ('SYSTEM', 'SYS')
      AND A.TEMPORARY = 'N'
      AND A.OWNER IN ()
      AND A.TABLE_NAME IN ();
  

• An Oracle data source

  SELECT
      NULL,
      A.OWNER,
      A.TABLE_NAME,
      A.NUM_ROWS,
      A.PARTITIONED,
      A.IOT_TYPE
  FROM
      ALL_TABLES A
  WHERE
      (A.OWNER, A.TABLE_NAME) NOT IN (
          SELECT
              OWNER,
              MVIEW_NAME
          FROM
              ALL_MVIEWS
          UNION
          ALL
          SELECT
              LOG_OWNER,
              LOG_TABLE
          FROM
              ALL_MVIEW_LOGS
      )
      AND A.OWNER NOT IN ('SYSTEM', 'SYS')
      AND A.TEMPORARY = 'N'
      AND A.OWNER IN ()
      AND A.TABLE_NAME IN ();
  

Filter out external tables

By default, the data migration service filters out external tables in a data migration task. The SQL statements for filtering external tables in data sources of different types are described as follows:

• An Oracle data source

  SELECT NULL,A.OWNER,A.TABLE_NAME,A.NUM_ROWS,A.PARTITIONED,A.IOT_TYPE
          FROM ALL_TABLES A
          WHERE (A.OWNER,A.TABLE_NAME) NOT IN ( SELECT OWNER,MVIEW_NAME FROM ALL_MVIEWS UNION ALL SELECT LOG_OWNER,LOG_TABLE FROM ALL_MVIEW_LOGS)
          AND A.OWNER NOT IN ('SYSTEM','SYS') AND A.TEMPORARY='N' AND A.EXTERNAL != 'YES'
          %s
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT /*HINT*/ NULL,A.OWNER,A.TABLE_NAME,A.NUM_ROWS,A.PARTITIONED,A.IOT_TYPE FROM ALL_TABLES A
          WHERE A.TABLE_NAME IN (
          SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE UPPER(OBJECT_TYPE)='TABLE'
          ) AND A.OWNER NOT IN ('SYSTEM','SYS') AND A.TEMPORARY='N' AND A.EXTERNAL != 'YES'
          %s
  

Query standard views

The SQL statements for querying standard views in data sources of different types are described as follows:

• A MySQL data source

  SELECT
      TABLE_NAME,
      TABLE_SCHEMA,
      VIEW_DEFINITION
      FROM
      information_schema.views
      WHERE
      TABLE_SCHEMA IN (); 
  

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      TABLE_NAME,
      TABLE_SCHEMA,
      VIEW_DEFINITION
  FROM
      information_schema.views
  WHERE
      TABLE_SCHEMA IN ();
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      VIEW_NAME,
      OWNER
  FROM
      ALL_VIEWS
  WHERE
      OWNER IN ()
  ORDER BY
      VIEW_NAME;
  

• An Oracle data source

  SELECT
      OBJECT_NAME,
      OWNER
  FROM
      ALL_OBJECTS
  WHERE
      OBJECT_TYPE='VIEW'
  AND
      STATUS='VALID'
  AND
      OWNER IN ();
  

Query materialized views

The SQL statements for querying materialized views in an Oracle data source are described as follows:

SELECT
    MVIEW_NAME AS NAME,
    OWNER
FROM
    ALL_MVIEWS
WHERE
    OWNER IN ()
UNION
SELECT
    LOG_TABLE AS NAME,
    LOG_OWNER
FROM
    ALL_MVIEW_LOGS
WHERE
    LOG_OWNER IN ();

Query tables with a primary key

The SQL statements for querying tables with a primary key in data sources of different types are described as follows:

• A MySQL data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.statistics
  WHERE
      TABLE_SCHEMA IN ()
      AND upper(INDEX_NAME) = 'PRIMARY';
  

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.statistics
  WHERE
      TABLE_SCHEMA IN ()
      AND upper(INDEX_NAME) = 'PRIMARY';
  

• An Oracle data source

  SELECT
      TABLE_NAME,
      OWNER
  FROM
      ALL_CONSTRAINTS
  WHERE
      OWNER IN ()
      AND STATUS = 'ENABLED'
      AND VALIDATED = 'VALIDATED'
      AND CONSTRAINT_TYPE = 'P';
  

Query tables with a primary key or a non-null unique key

The SQL statements for querying tables with a primary key or a non-null unique key in data sources of different types are described as follows:

• A MySQL data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.statistics
  WHERE
      TABLE_SCHEMA IN ()
  GROUP BY
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME
  HAVING
      count(*) = count(
          IF(
              upper(nullable) != 'YES'
              AND NON_UNIQUE = 0,
              1,
              NULL
          )
      );
  

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.statistics
  WHERE
      TABLE_SCHEMA IN ()
  GROUP BY
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME
  HAVING
      count(*) = count(
          IF(
              upper(nullable) != 'YES'
              and NON_UNIQUE = 0,
              1,
              NULL
          )
      );
  

• An Oracle data source

  SELECT
      TABLE_NAME,
      OWNER
  FROM
      ALL_CONSTRAINTS
  WHERE
      OWNER IN ()
      AND STATUS = 'ENABLED'
      AND VALIDATED = 'VALIDATED'
      AND CONSTRAINT_TYPE = 'P';
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      DISTINCT TABLE_NAME,
      OWNER
  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 IN ()
              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 IN ()
      );
  

Query tables with UNIQUE function indexes

The SQL statements for querying tables with UNIQUE function indexes in data sources of different types are described as follows:

• An Oracle data source

  SELECT
      DISTINCT A.TABLE_NAME,
      A.TABLE_OWNER
  FROM
      ALL_IND_EXPRESSIONS A
      JOIN ALL_INDEXES B ON A.TABLE_OWNER = B.TABLE_OWNER
      AND A.TABLE_NAME = B.TABLE_NAME
      AND A.INDEX_NAME = B.INDEX_NAME
  WHERE
      A.TABLE_OWNER IN ()
      AND B.UNIQUENESS = 'UNIQUE';
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      DISTINCT A.TABLE_NAME,
      A.TABLE_OWNER
  FROM
      ALL_IND_EXPRESSIONS A
      JOIN ALL_INDEXES B ON A.TABLE_OWNER = B.TABLE_OWNER
      AND A.TABLE_NAME = B.TABLE_NAME
      AND A.INDEX_NAME = B.INDEX_NAME
  WHERE
      A.TABLE_OWNER IN ()
      AND B.UNIQUENESS = 'UNIQUE';
  

Query tables with specific data types

The SQL statements for querying tables with specific data types in data sources of different types are described as follows:

• An Oracle data source

  SELECT
      DISTINCT TABLE_NAME AS TABLENAME,
      OWNER AS DBNAME
  FROM
      all_tab_columns
  WHERE
      OWNER IN ()
      AND DATA_TYPE IN / NOT IN ();
  

• A MySQL data source

  SELECT
      DISTINCT TABLE_NAME AS TABLENAME,
      TABLE_SCHEMA
  FROM
      information_schema.COLUMNS
  WHERE
      TABLE_SCHEMA IN ()
      AND DATA_TYPE IN / NOT IN ();
  

Query tables with a primary key and specific data types

The SQL statements for querying tables with a primary key and specific data types in data sources of different types are described as follows:

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.COLUMNS
  WHERE
      UPPER(COLUMN_KEY) = 'PRI'
      AND TABLE_SCHEMA IN ()
      AND UPPER(COLUMN_TYPE) IN ();
  

• A MySQL data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.COLUMNS
  WHERE
      UPPER(COLUMN_KEY) = 'PRI'
      AND TABLE_SCHEMA IN ()
      AND UPPER(COLUMN_TYPE) IN ();
  

Query tables with LOB types

The SQL statements for querying tables with large object (LOB) types in data sources of different types are described as follows:

• An Oracle data source

  SELECT
      DISTINCT TABLE_NAME,
      OWNER
  FROM
      ALL_LOBS
  WHERE
      OWNER IN ();
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      DISTINCT TABLE_NAME,
      OWNER
  FROM
      ALL_TAB_COLUMNS
  WHERE
      DATA_TYPE IN ('BLOB', 'CLOB')
      AND OWNER IN ();
  

Query tables whose row_movement is set to DISABLED in an Oracle database

SELECT
    TABLE_NAME,
    OWNER
FROM
    ALL_TABLES
WHERE
    OWNER IN ()
    AND ROW_MOVEMENT = 'DISABLED';

Query tables with pseudo columns in OceanBase Database

• A MySQL-compatible tenant of an OceanBase data source

  SELECT
      DISTINCT TABLE_NAME,
      TABLE_SCHEMA
  FROM
      information_schema.columns
  WHERE
      TABLE_SCHEMA IN ()
      AND COLUMN_NAME in (
          'OMS_PK_INCRMT',
          'OMS_OBJECT_NUMBER',
          'OMS_RELATIVE_FNO',
          'OMS_BLOCK_NUMBER',
          'OMS_ROW_NUMBER'
      );
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      DISTINCT TABLE_NAME,
      OWNER
  FROM
      ALL_TAB_COLUMNS
  WHERE
      OWNER IN ()
      AND COLUMN_NAME IN (
          'OMS_PK_INCRMT',
          'OMS_OBJECT_NUMBER',
          'OMS_RELATIVE_FNO',
          'OMS_BLOCK_NUMBER',
          'OMS_ROW_NUMBER'
      );
  

Query tables with more columns than the specified value

The SQL statements for querying tables with more columns than the specified value in data sources of different types are described as follows:

• An Oracle data source

  SELECT
      TABLE_NAME,
      OWNER
  FROM
      ALL_TAB_COLUMNS
  WHERE
      OWNER = ?
  GROUP BY
      TABLE_NAME,
      OWNER
  HAVING COUNT(1) > ?;
  

• An Oracle-compatible tenant of an OceanBase data source

  SELECT
      /*+ query_timeout(600000000)*/
      TABLE_NAME,
      OWNER
  FROM
      ALL_TAB_COLUMNS
  WHERE
      OWNER = ?
  GROUP BY
      TABLE_NAME,
      OWNER
  HAVING COUNT(1) > ?;