wordpress的商城网站制作公司,百度账号申请注册,网站开发报价合同,宝塔面板WordPress优化文章目录 一、GaussDB系统视图归纳总结二、常用运维SQL 一、GaussDB系统视图归纳总结
GaussDB 是华为基于开源数据库PostgreSQL开发的商业数据库#xff0c;在兼容oracle和mysql上做出了不少努力#xff0c;GaussDB的系统视图对标Oracle#xff0c;有以下对应关系#xff… 文章目录 一、GaussDB系统视图归纳总结二、常用运维SQL 一、GaussDB系统视图归纳总结
GaussDB 是华为基于开源数据库PostgreSQL开发的商业数据库在兼容oracle和mysql上做出了不少努力GaussDB的系统视图对标Oracle有以下对应关系
oracle系统视图对应GaussDB系统视图含义DBA_*ADM_*DBA拥有或可访问的所有对象ALL_*DB_*某用户拥有或可访问的所有对象USER_*MY_*某用户拥有的所有对象
以及GaussDB 自己定义的gs_*系统表和视图 因为以上原因常用运维SQL涉及GaussDB自己的系统视图和pg的系统表和对标oracle的系统视图(缝合怪。
序号系统视图1my_tables2adm_tab_columns3my_indexes4pg_partition5information_schema.table_privileges6adm_indexes7pg_class8pg_proc9gs_package10pg_stat_activity11pg_lock_status12pg_locks13pg_query_audit14pg_setting15pg_database
二、常用运维SQL
其实很多运维功能在管理平台都可以获取但我们可以通过这些运维sql了解管理平台获取这些信息的原理。
统计表数量和表数据量
select count(*) ,num_rows from my_tables where owner XXX;
查询表字段
select * from adm_tab_columns where ownerXXX and table_name balabala;
查看表字段数量
select table_name,count(1) from adm_tab_columns where table_nameXXX;
查询表定义
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 prokindf;
查询存储过程数量
select count(*) from pg_proc where prokind p;
查询函数、存储过程定义
select pg_get_functiondef(oid) from pg_proc where pronameXXX and pronamespace (select oid from pg_namespace where nspname);
查询package数量
select count(*) from gs_package where pkgowner(select oid from pg_authid where relnameXXX);
查询package定义
select CREATE OR REPLACE PACKAGE||pkgname||AS --||pkgspecsrc||||pkgname||; from gs_package where pkgnameXXX;
查看活跃会话
select pid,substr(query,1,100),query_start,sysdate-query_start as runtime,application_name from pg_stat_activity where stateactive and usenamecurrent_user;
终止会话
select pg_terminate_backend(pid);
死锁查询
select pid,query from pg_stat_activity where datnameXXX 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 pidXXXXXXXXXXX and relation is not null)
查看审计日志
select * from pg_query_audit(2024-06-12 10:00:00,2024-6-12 11:00:00) where usernameroot 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;
