--- title: OceanBase 数据库 V3.x 如何查询视图的列-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库 V3.x 如何查询视图的列相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 V3.x 如何查询视图的列的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库 V3.x 如何查询视图的列 更新时间：2026-05-25 09:11 适用版本： V3.1.x、V3.2.x 内容类型：How-to 由于 OceanBase 数据库 V3.x 通过兼容 MySQL 或 Oracle 的视图都不包含视图的列，本文介绍如何通过 OceanBase 数据库 V3.x 的系统视图查询视图的列。 ## 适用版本 OceanBase 数据库 V3.1.x、V3.2.x 版本 ## OceanBase 数据库 V3.x MySQL 模式 ### 方式一: 可以通过如下 SQL 先查询视图的 table_id，再根据 table_id 查询视图的列 注意：由于 `oceanbase.__tenant_virtual_table_column` 是用于 SHOW 功能的，只能根据 table_id 查询。 首先，在 OCP 上租户页面查看租户 ID 或通过 root@sys 登录查询系统租户。 ```shell obclient [oceanbase]> select tenant_id,tenant_name from oceanbase.__all_tenant; +-----------+-------------+ | tenant_id | tenant_name | +-----------+-------------+ | 1 | sys | | 1005 | oboracle | | 1012 | obmysql | +-----------+-------------+ 2 rows in set (0.00 sec) ``` 然后根据如下 SQL 查询用户租户 (如下 1012 为 tenant_id, 40 为固定值，50133 为 table_id)。 ```shell obclient> select table_id from oceanbase.__all_table_v2 where table_name = 'test_vw'; obclient> select * from oceanbase.__tenant_virtual_table_column where table_id= (1012<<40)+50133; ``` 详细测试用例如下。 ```shell obclient [test]> create view test_vw as select * from tbl1; Query OK, 0 rows affected (0.02 sec) ``` ```shell obclient [test]> select table_id from oceanbase.__all_table_v2 where table_name = 'test_vw'; +----------+ | table_id | +----------+ | 50133 | +----------+ 1 row in set (0.00 sec) ``` ```shell obclient [test]> select * from oceanbase.__tenant_virtual_table_column where table_id= (1012<<40)+50133; +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ | table_id | field | type | collation | null | key | default | extra | privileges | comment | +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ | 1112705767359445 | PK | int(11) | | YES | | NULL | | | | | 1112705767359445 | name | varchar(25) | | YES | | NULL | | | | +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ ``` 注意：由于 `oceanbase.__tenant_virtual_table_column` 是用于 SHOW 功能的，只能根据 table_id (含 tenant_id) 查询。 ```shell obclient [test]> select * from oceanbase.__tenant_virtual_table_column ; ERROR 4016 (HY000): this table is used for show clause, can't be selected obclient [test]> select * from oceanbase.__tenant_virtual_table_column where table_id= 50133; ERROR 1210 (HY000): Invalid argument ``` ### 方式二: 可以通过 show create table test_vw 或 show create view test_vw 方式查询。 ```shell obclient> show create table test_vw; obclient> show create view test_vw; ``` 详细测试用例如下。 ```shell obclient [test]> show create table test_vw; +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | test_vw | CREATE VIEW `test_vw` AS select `test`.`tbl1`.`PK` AS `PK`,`test`.`tbl1`.`name` AS `name` from `test`.`tbl1` | utf8mb4 | utf8mb4_general_ci | +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec) ``` ```shell obclient [test]> show create view test_vw; +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | test_vw | CREATE VIEW `test_vw` AS select `test`.`tbl1`.`PK` AS `PK`,`test`.`tbl1`.`name` AS `name` from `test`.`tbl1` | utf8mb4 | utf8mb4_general_ci | +---------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec) ``` ## OceanBase 数据库 V3.x Oracle 模式 ### 方式一: 可以通过如下 SQL 先查询视图的 table_id，再根据 table_id 查询视图的列 注意：由于 `sys.tenant_virtual_table_column` 是用于 SHOW 功能的，只能根据 table_id 查询。 ```shell obclient> select table_id from sys.all_virtual_table_agent where table_name=upper('test_vw'); obclient> select * from sys.tenant_virtual_table_column where table_id=1105009185965749; ``` ### 方式二: 可以通过 desc test_vw 方式查询 详细测试用例如下。 ```shell Server version: OceanBase 3.2.4.6 (r106000052023102710-3b2156f1ff80e949c7229d0f61eb36deb4197d87) (Built Oct 27 2023 10:24:33) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [ALVIN]> create view test_vw as select * from test; Query OK, 0 rows affected (0.064 sec) ``` ```shell obclient [ALVIN]> select table_id from sys.all_virtual_table_agent where table_name=upper('test_vw'); +------------------+ | TABLE_ID | +------------------+ | 1105009185965749 | +------------------+ 1 row in set (0.005 sec) ``` ```shell obclient [ALVIN]> select * from sys.tenant_virtual_table_column where table_id=1105009185965749; +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ | TABLE_ID | FIELD | TYPE | COLLATION | NULL | KEY | DEFAULT | EXTRA | PRIVILEGES | COMMENT | +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ | 1105009185965749 | N1 | NUMBER(*,0) | NULL | YES | NULL | NULL | NULL | NULL | NULL | | 1105009185965749 | D1 | DATE | NULL | YES | NULL | NULL | NULL | NULL | NULL | +------------------+-------+-------------+-----------+------+-----+---------+-------+------------+---------+ 2 rows in set (0.003 sec) ``` ```shell obclient [ALVIN]> desc test_vw; +-------+-------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+-------------+------+-----+---------+-------+ | N1 | NUMBER(*,0) | YES | NULL | NULL | NULL | | D1 | DATE | YES | NULL | NULL | NULL | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.003 sec) ``` 由于 sys.tenant_virtual_table_column 是用于 SHOW 功能的，只能根据 table_id 查询。 ```shell obclient [ALVIN]> select * from sys.tenant_virtual_table_column where table_id in (select table_id from sys.all_virtual_table_agent where table_name=upper('test_vw')); ORA-00600: internal error code, arguments: -4016, this table is used for show clause, can't be selected obclient [ALVIN]> select * from sys.tenant_virtual_table_column ; ORA-00600: internal error code, arguments: -4016, this table is used for show clause, can't be selected ``` Previous [gv$plan_cache_plan_stat 中的 PARAM_INFOS 的含义](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000480903) Next [CONSISTENCY_LEVEL 字段为-1](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000000089) ![有帮助](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) 咨询热线