---
title: "查询数据库对象 | OceanBase 文档中心"
description: 查询数据库对象 您在创建数据迁移或数据同步任务时，指定需要迁移或同步的对象后，OceanBase 迁移服务（OceanBase Migration Service，OMS） 会自动过滤不支持的表，可能会导致识别出的表数量和实际需要迁移或同步的表数量不一致，您可以通过本文的 SQL 语句进行查询。 查询表的基本信息 不…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*L03BS6f-o40AAAAAAAAAAAAADiGDAQ/original) 迁移服务 OMSV 4.3.1 企业版

# 查询数据库对象

更新时间：2026-05-14 11:20:50

您在创建数据迁移或数据同步任务时，指定需要迁移或同步的对象后，OceanBase 迁移服务（OceanBase Migration Service，OMS） 会自动过滤不支持的表，可能会导致识别出的表数量和实际需要迁移或同步的表数量不一致，您可以通过本文的 SQL 语句进行查询。

## 查询表的基本信息

不同数据源类型查询表基本信息的 SQL 语句如下。

- MySQL 数据源

  ```SQL
  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 兼容模式数据源

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

  ```
 - DB2 LUW 数据源

  ```SQL
  SELECT
      TABSCHEMA,
      TABNAME,
      CARD
  FROM
      SYSCAT.TABLES
  WHERE
      OWNERTYPE = 'U'
      AND TYPE = 'T'
      AND TABSCHEMA NOT IN(
          'NULLID',
          'SQLJ',
          'SYSCAT',
          'SYSFUN',
          'SYSIBM',
          'SYSIBMADM',
          'SYSIBMINTERNAL',
          'SYSIBMTS',
          'SYSPROC',
          'SYSPUBLIC',
          'SYSSTAT',
          'SYSTOOLS'
      )
      AND TABSCHEMA IN ()
      AND TABNAME IN ();

  ```
 - OceanBase 数据库 Oracle 兼容模式数据源

  ```SQL
  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 数据源

  ```SQL
  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 数据源

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

  ```

## 过滤外表

OMS 会默认过滤掉外表的迁移/同步，不同数据源类型过滤外表的 SQL 语句如下。

- Oracle 数据源

  ```SQL
  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

  ```SQL
  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 语句如下。

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

  ```SQL
  SELECT
      VIEWNAME,
      VIEWSCHEMA
  FROM
      SYSCAT.VIEWS
  WHERE
      VIEWSCHEMA IN ()
  ORDER BY
      VIEWNAME;

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

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

  ```

## 查询物化视图

Oracle 数据源查询物化视图的 SQL 语句如下。

```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 语句如下。

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

  ```
 - OceanBase 数据源 MySQL 兼容模式

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

  ```

## 查询有主键或非空唯一键的表

不同数据源类型查询有主键或非空唯一键的表的 SQL 语句如下。

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

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

  ```SQL
  SELECT
      DISTINCT TABNAME,
      TABSCHEMA
  FROM
      SYSCAT.INDEXES
  WHERE
      TABSCHEMA IN ()
      AND UNIQUERULE IN ('U', 'P')
  ORDER BY
      TABNAME;

  ```

## 查询有 UNIQUE 函数索引的表

不同数据源类型查询有 UNIQUE 函数索引的表的 SQL 语句如下。

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

  ```SQL
  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 语句如下。

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

  ```
 - MySQL 数据源

  ```SQL
  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 兼容模式数据源

  ```SQL
  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 语句如下。

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

  ```SQL
  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 的表

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

```

## 查询 DB2 LUW 数据库中 DATA_CAPTURE 为 ENABLED 的表

```SQL
SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES
WHERE TABSCHEMA IN () AND DATACAPTURE in ('Y', 'L');

```

## 查询 OceanBase 数据库中包含 pseudo 列的表

- OceanBase 数据库 MySQL 兼容模式

  ```SQL
  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 兼容模式

  ```SQL
  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 语句如下。

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

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

  ```

## 查询源端 Oracle 数据库的 PL 对象

- 查询用户所有的存储过程对象

  ```SQL
  SELECT
      OBJECT_NAME
  FROM
      ALL_OBJECTS
  WHERE  
      OWNER = ?
  AND  
      OBJECT_TYPE  = 'PROCEDURE'
  AND  
      STATUS = 'VALID';

  ```
 - 查询用户所有的函数对象

  ```SQL
  SELECT
      OBJECT_NAME
  FROM
      ALL_OBJECTS
  WHERE
      OWNER = ?
  AND
      OBJECT_TYPE = 'FUNCTION'
  AND
      STATUS = 'VALID';

  ```
 - 查询用户所有的序列对象

  ```SQL
  SELECT
      OBJECT_NAME
  FROM
      ALL_OBJECTS
  WHERE
      OWNER = ?
  AND
      OBJECT_TYPE = 'SEQUENCE'
  AND
      STATUS = 'VALID'
  AND
      GENERATED = 'N';

  ```
 - 查询用户所有的同义词对象

  ```SQL
  SELECT
      OBJECT_NAME
  FROM
      ALL_OBJECTS
  WHERE
      OWNER = ?
  AND
      OBJECT_TYPE = 'SYNONYM'
  AND
      STATUS = 'VALID';

  ```
 - 查询用户所有的包对象

  ```SQL
  SELECT
      OBJECT_NAME
  FROM
      ALL_OBJECTS pkg
  WHERE
      pkg.OBJECT_TYPE = 'PACKAGE'
      AND pkg.STATUS = 'VALID'
      AND pkg.OWNER = ?
      AND EXISTS (
          SELECT 1
          FROM   ALL_OBJECTS body
          WHERE  body.OBJECT_NAME  = pkg.OBJECT_NAME
          AND  body.OWNER = pkg.OWNER
          AND  body.OBJECT_TYPE = 'PACKAGE BODY'
          AND  body.STATUS = 'VALID'
      );

  ```
 - 查询用户所有的自定义类型

  ```SQL
  SELECT
      a.OBJECT_NAME
  FROM
      ALL_OBJECTS a
  LEFT JOIN
      ALL_OBJECTS b
      ON  a.OWNER = b.OWNER
      AND a.OBJECT_NAME = b.OBJECT_NAME
      AND b.OBJECT_TYPE = 'TYPE BODY'
  WHERE
      a.OWNER = ?
      AND a.OBJECT_TYPE = 'TYPE'
      AND a.STATUS = 'VALID'
      AND (
          b.OBJECT_NAME IS NULL
          OR b.STATUS = 'VALID'
      );

  ```
 - 查询用户所有有效的触发器

  ```SQL
  SELECT DISTINCT
      T.TABLE_OWNER,
      T.TABLE_NAME,
      T.TRIGGER_NAME
  FROM
      all_triggers T,
      all_objects O
  WHERE
      T."OWNER" = O."OWNER"
      AND T."TRIGGER_NAME" = O."OBJECT_NAME"
      AND O."OBJECT_TYPE"= 'TRIGGER'
      AND O."STATUS" = 'VALID'
      AND O."OWNER" = ?;

  ```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线