SQL statements for querying table objects - ApsaraDB for OceanBase

When you create a data migration or synchronization task, the data transmission service 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:

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

OceanBase data source in MySQL-compatible mode

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 data source in Oracle-compatible mode

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

PostgreSQL data source

SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME
    FROM
    information_schema.tables
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA <> 'pg_catalog'
    AND TABLE_SCHEMA <> 'information_schema'
    AND TABLE_SCHEMA IN ()
    AND TABLE_NAME IN ();

Filter out external tables

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

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

OceanBase data source in Oracle-compatible mode

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:

MySQL data source

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

OceanBase data source in MySQL-compatible mode

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

OceanBase data source in Oracle-compatible mode

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

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 Oracle data sources 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:

MySQL data source

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

OceanBase data source in MySQL-compatible mode

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

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:

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

OceanBase data source in MySQL-compatible mode

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 data source

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

OceanBase data source in Oracle-compatible mode

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 a unique index

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

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

OceanBase data source in Oracle-compatible mode

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:

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

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:

OceanBase data source in MySQL-compatible mode

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 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:

Oracle data source

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

OceanBase data source in Oracle-compatible mode

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 containing pseudocolumns in an OceanBase database

OceanBase data source in MySQL-compatible mode

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 data source in Oracle-compatible mode

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 containing more columns than the specified value

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

Oracle data source

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

OceanBase data source in Oracle-compatible mode

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