如何通过脚本在字典视图中查询元数据

本文介绍如何通过脚本扫描数据字典，找到可能的关键字或关键字表。

1. 创建脚本 find_meta.sql，脚本内容如下。

   SELECT DISTINCT name,info FROM __all_virtual_sys_parameter_stat 
   WHERE name LIKE concat('%',@mykeyvalue,'%') OR info LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT name,info FROM __all_sys_variable 
   WHERE name LIKE concat('%',@mykeyvalue,'%') OR info LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT table_name FROM __all_virtual_table 
   WHERE table_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT t2.table_name,t1.column_name FROM __all_column t1,__all_virtual_table t2 
   WHERE t1.table_id = t2.table_id and column_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT name,display_name FROM v$event_name  
   WHERE name LIKE concat('%',@mykeyvalue,'%') OR DISPLAY_NAME LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT name,display_name FROM v$statname  
   WHERE name LIKE concat('%',@mykeyvalue,'%') OR DISPLAY_NAME LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT name FROM __all_virtual_latch 
   WHERE name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT cache_name FROM __all_virtual_kvcache_info 
   WHERE cache_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT free_list_name FROM __all_virtual_concurrency_object_pool 
   WHERE free_list_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT pcode_name FROM __all_virtual_obrpc_stat 
   WHERE pcode_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   
   SELECT DISTINCT context FROM v$memORy 
   WHERE context LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT mod_name FROM __all_virtual_memORy_info 
   WHERE mod_name LIKE concat('%',@mykeyvalue,'%') ORDER BY 1;
   
   SELECT DISTINCT pcode_name FROM v$obrpc_incoming pcode_name LIKE concat('%',@mykeyvalue,'%') 
   UNION
   SELECT DISTINCT pcode_name FROM v$obrpc_outcoming pcode_name LIKE concat('%',@mykeyvalue,'%');
   

2. 将脚本文件移动到 OceanBase 数据库安装目录的 admin 目录下。

   [admin@hostname ~]$ cp find_meta.sql /home/admin/oceanbase/admin/
   

3. 通过客户端连接 OceanBase 数据库。

4. 设置查找的元数据关键字。

   obclient> SET @mykeyvalue='leak';
   

5. 执行脚本。

   obclient> source find_meta.sql;
   

   示例输出如下。