首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
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 登录查询系统租户。
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)。
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;
详细测试用例如下。
obclient [test]> create view test_vw as select * from tbl1;
Query OK, 0 rows affected (0.02 sec)
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)
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) 查询。
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 方式查询。
obclient> show create table test_vw;
obclient> show create view test_vw;
详细测试用例如下。
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)
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 查询。
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 方式查询
详细测试用例如下。
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)
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)
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)
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 查询。
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
