テーブルオブジェクトをクエリするSQL

データ移行タスクを新規作成する際、移行対象となるオブジェクトを指定すると、データ移行サービスはサポートされていないテーブルを自動的に除外します。そのため、認識されたテーブル数と実際に移行が必要なテーブル数が一致しない場合があります。本記事で紹介するSQL文を使用してクエリすることができます。

テーブルの基本情報をクエリする

異なるデータソースタイプでテーブルの基本情報をクエリするSQL文は以下の通りです。

• MySQLデータソース

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

• OceanBaseデータベースMySQL互換モードテナントデータソース

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

• OceanBaseデータベースOracle互換モードテナントデータソース

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

• Oracleデータソース

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

外部テーブルのフィルタリング

データ移行では、外部テーブルの移行はデフォルトで除外されます。異なるデータソースタイプで外部テーブルをフィルタリングするSQL文は以下の通りです。

• Oracleデータソース

  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
  

• OceanBaseデータベースOracle互換モードテナントデータソース

  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
  

ビューをクエリする

異なるデータソースタイプでビューをクエリするSQL文は以下の通りです。

• MySQLデータソース

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

• OceanBaseデータベースMySQL互換モードテナントデータソース

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

• OceanBaseデータベースのOracle互換モードテナントデータソース

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

• Oracleデータソース

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

マテリアライズドビューのクエリ

OracleデータソースでマテリアライズドビューをクエリするSQLステートメントは以下のとおりです。

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

主キーを持つテーブルのクエリ

異なるデータソースタイプで主キーを持つテーブルをクエリするSQLステートメントは以下のとおりです。

• MySQLデータソース

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

• OceanBaseデータソースのMySQL互換モードテナント

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

• Oracleデータソース

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

主キーまたはNULLではない一意キーを持つテーブルのクエリ

異なるデータソースタイプで主キーまたはNULLではない一意キーを持つテーブルをクエリするSQLステートメントは以下のとおりです。

• MySQLデータソース

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

• OceanBaseデータベースのMySQL互換モードテナントデータソース

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

• Oracleデータソース

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

• OceanBaseデータベースのOracle互換モードテナントデータソース

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

UNIQUE関数インデックスを持つテーブルのクエリ

異なるデータソースタイプでUNIQUE関数インデックスを持つテーブルをクエリするSQLステートメントは以下のとおりです。

• Oracleデータソース

  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';
  

• OceanBaseデータベースOracle互換モードテナントデータソース

  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';
  

特定のデータ型を含むテーブルの存在確認

異なるデータソースで特定のデータ型を含むテーブルの存在を確認するSQLステートメントは以下のとおりです。

• Oracleデータソース

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

• MySQLデータソース

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

主キーを持ち、特定のデータ型を含むテーブルのクエリ

異なるデータソースタイプで主キーを持ち、特定のデータ型を含むテーブルをクエリするSQLステートメントは以下のとおりです。

• OceanBaseデータベースMySQL互換モードテナントデータソース

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

• MySQLデータソース

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

LOB型を含むテーブルのクエリ

異なるデータソースタイプでLOB型を含むテーブルをクエリするSQLステートメントは以下のとおりです。

• Oracleデータソース

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

• OceanBaseデータベースOracle互換モードテナントデータソース

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

Oracleデータベースにおけるrow_movementがDISABLEDのテーブルのクエリ

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

pseudo列を含むテーブルのクエリ

• OceanBaseデータベースMySQL互換モードのテナント

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

• OceanBaseデータベースOracle互換モードのテナント

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

列数が指定値を超えるテーブルのクエリ

異なるデータソースタイプで列数が指定値を超えるテーブルをクエリするSQLステートメントは以下のとおりです。

• Oracleデータソース

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

• OceanBaseデータベースOracle互換モードのテナントデータソース

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