SHOW OPEN TABLES
功能描述
SHOW OPEN TABLES用于列出所有被打开的非临时表。
注意事项
- 该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
- 在MySQL数据库的
show open tables结果中Database字段是库名，Vastbase中是表所在的模式名。
语法格式
SHOW OPEN TABLES
[{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
参数说明
[{FROM | IN} schema_name]
指定查询的模式，FROM和IN两者是等价的。
[LIKE 'pattern' | WHERE expr]
pattern支持like语法，用于模糊查询；expr支持任意表达式，通常的用法是：
show open tables where Database = 'schema_name'; show open tables where "Table" = 'table_name';
返回结果集
| 字段 | 说明 |
|---|---|
| Database | 表所在的模式名。 |
| Table | 表名。 |
| In_use | 表的锁或锁请求数。例如：
LOCK TABLE t1 WRITE获取表锁，则In_use将为1。LOCK TABLE t1 WRITE，则客户端将进入锁等待，此时In_use为2。 |
| Name_locked | 表是否被ACCESS EXCLUSIVE锁定。 |
示例
1、查询当前所有被打开的非临时表。
show open tables;
返回结果为：
Database | Table | In_use | Name_locked
------------+-----------------------+--------+-------------
pg_catalog | pg_type | 0 | 0
pg_catalog | pg_authid | 0 | 0
pg_catalog | pg_statistic | 0 | 0
pg_catalog | pg_settings | 0 | 0
pg_catalog | pg_stat_replication | 0 | 0
pg_catalog | pg_subscription | 0 | 0
pg_catalog | pg_class | 0 | 0
pg_catalog | pg_attribute | 0 | 0
pg_catalog | pg_proc | 0 | 0
pg_catalog | pg_partition | 0 | 0
pg_catalog | pg_index | 0 | 0
pg_catalog | pg_operator | 0 | 0
pg_catalog | pg_opclass | 0 | 0
pg_catalog | pg_am | 0 | 0
pg_catalog | pg_amop | 0 | 0
pg_catalog | pg_amproc | 0 | 0
pg_catalog | pg_rewrite | 0 | 0
pg_catalog | pg_event_trigger | 0 | 0
pg_catalog | pg_cast | 0 | 0
pg_catalog | pg_namespace | 0 | 0
pg_catalog | pg_database | 0 | 0
pg_catalog | pg_db_role_setting | 0 | 0
pg_catalog | pg_tablespace | 0 | 0
pg_catalog | pg_auth_members | 0 | 0
pg_catalog | pg_extension | 0 | 0
pg_catalog | pg_foreign_server | 0 | 0
pg_catalog | pgxc_node | 0 | 0
pg_catalog | pgxc_group | 0 | 0
pg_catalog | pg_resource_pool | 0 | 0
pg_catalog | pg_default_acl | 0 | 0
pg_catalog | gs_client_global_keys | 0 | 0
pg_catalog | pg_job | 0 | 0
pg_catalog | pg_object | 0 | 0
pg_catalog | pg_synonym | 0 | 0
pg_catalog | gs_recyclebin | 0 | 0
pg_catalog | gs_txn_snapshot | 0 | 0
pg_catalog | gs_sql_patch | 0 | 0
pg_catalog | pg_user_status | 0 | 0
pg_catalog | pg_auth_history | 0 | 0
(39 rows)
2、查询表名中包含am的表。
show open tables like '%am%';
返回结果为：
Database | Table | In_use | Name_locked
------------+--------------+--------+-------------
pg_catalog | pg_am | 0 | 0
pg_catalog | pg_amop | 0 | 0
pg_catalog | pg_amproc | 0 | 0
pg_catalog | pg_namespace | 0 | 0
(4 rows)
3、使用where选项查询指定表。
show open tables where "Table" = 'pg_amop';
返回结果为：
Database | Table | In_use | Name_locked
------------+---------+--------+-------------
pg_catalog | pg_amop | 0 | 0
(1 row)
4、锁定表pg_amop。
lock tables pg_amop read;
5、查询当前含有锁的表。
show open tables where In_use > 0;
返回结果为：
Database | Table | In_use | Name_locked
------------+---------+--------+-------------
pg_catalog | pg_amop | 1 | 0
(1 row)
6、解锁。
unlock tables;
