GaussDB系统视图归纳总结及常用运维SQL

一、GaussDB系统视图归纳总结

GaussDB 是华为基于开源数据库PostgreSQL开发的商业数据库，在兼容oracle和mysql上做出了不少努力，GaussDB的系统视图对标Oracle，有以下对应关系：

oracle系统视图	对应GaussDB系统视图	含义
DBA_*	ADM_*	DBA拥有或可访问的所有对象
ALL_*	DB_*	某用户拥有或可访问的所有对象
USER_*	MY_*	某用户拥有的所有对象

以及GaussDB 自己定义的gs_*系统表和视图
因为以上原因，常用运维SQL涉及GaussDB自己的系统视图和pg的系统表和对标oracle的系统视图(缝合怪？）。

序号	系统视图
1	my_tables
2	adm_tab_columns
3	my_indexes
4	pg_partition
5	information_schema.table_privileges
6	adm_indexes
7	pg_class
8	pg_proc
9	gs_package
10	pg_stat_activity
11	pg_lock_status
12	pg_locks
13	pg_query_audit
14	pg_setting
15	pg_database

二、常用运维SQL

其实很多运维功能在管理平台都可以获取，但我们可以通过这些运维sql了解管理平台获取这些信息的原理。

统计表数量和表数据量
select count(*) ,num_rows from my_tables where owner ='XXX';
查询表字段
select * from adm_tab_columns where owner='XXX' and table_name ='balabala';
查看表字段数量
select table_name,count(1) from adm_tab_columns where table_name='XXX';
查询表定义
select pg_get_tabledef('XXX');
查询视图定义
select pg_get_viewdef('XXX');
查询分区表
select partstrategy,count(1) from pg_partition group by 1;
查询表权限
select * from information_schema.table_privileges where grantee ='XXX' and grantor <> 'XXX';
查询表索引
select count(*),* from adm_indexes where owner ='';
查询索引定义
select pg_get_indexdef(oid) from pg_class where relname ='XXX';
查询序列数量
select count(*) from pg_class where relkind ='S' or relkind ='L';
查询函数数量
select count(*) from pg_proc where prokind='f';
查询存储过程数量
select count(*) from pg_proc where prokind ='p';
查询函数、存储过程定义
select pg_get_functiondef(oid) from pg_proc where proname='XXX' and pronamespace =(select oid from pg_namespace where nspname='');
查询package数量
select count(*) from gs_package where pkgowner=(select oid from pg_authid where relname='XXX');
查询package定义
select 'CREATE OR REPLACE PACKAGE'||pkgname||'AS --'||pkgspecsrc||''||pkgname||';' from gs_package where pkgname='XXX';
查看活跃会话
select pid,substr(query,1,100),query_start,sysdate-query_start as runtime,application_name from pg_stat_activity where state='active' and usename=current_user;
终止会话
select pg_terminate_backend(pid);
死锁查询
select pid,query from pg_stat_activity where datname='XXX' and query like '%drop %';
select pid,query from pg_stat_activity where pid in (select distinct pid from pg_lock_status() where relation on (select relation from pg_locks where pid='XXXXXXXXXXX' and relation is not null)
查看审计日志
select * from pg_query_audit('2024-06-12 10:00:00','2024-6-12 11:00:00') where username='root' and database ='XXX';
查看数据库大小
select datname,pg_database_size(datname)/1024/1024/1024 from pg_database;
查询guc参数
select name,setting from pg_setting order by name;
查询逻辑复制槽相关内容
select * from pg_relplicatio_slots where slot_type ='logical';
查询内存
select contextname,sum(totalsize)/1024/1024/ sum,count(*) from gs_session_memory_context group by contextname order by sum desc limit 20;
select * from gs_total_memory_detail;