金仓 Kingbase 日常运维 SQL 汇总

金仓 Kingbase 日常运维 SQL 汇总
1 单机启停

sys_ctl start|stop|restart 或指定data路径和端口等
sys_ctl start|stop|restart -D /data/kingbase/data -p 54322

2 集群启停

sys_monitor start|stop|restart

3 修改配置后重新加载

sys_ctl reload

4 初始化实例

initdb -E utf-8 -U system -D /home/kingbase/KingbaseES/data -A trust -m oracle -enable-ci

5 查看数据库模式

show database_mode ;

6 查看数据库授权过期时间

 select get_license_validdays();

7 查看编码

show server_encoding ;

8 创建数据库

create database	db1 owner user1 ;

9 授权数据库
这将授予 “username” 用户对 “dbname” 数据库的完全权限。

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

10 创建用户
这将创建一个名为 “username” 的新用户，并为其设置密码为 “password”。

CREATE USER username WITH PASSWORD 'password';

11 授权某个模式给其他用户的 SQL 指令

GRANT ALL ON SCHEMA schema_name TO user_name;

12 授权某个表或视图给其他用户的 SQL 指令

GRANT ALL ON table_name TO user_name; 
GRANT ALL ON view_name TO user_name;

13 授予用户对表或视图中特定列的访问权限

GRANT SELECT (column_name1, column_name2, ...) ON table_name TO user_name;

14 创建用户并赋权超级用户角色

create user oa1 with password '12345';
alter user oa1 superuser ;

15 收回用户的超级用户角色

alter user oa1 nosuperuser ;

16 修改用户密码

alter user oa1 password '123';

17 查看数据库

select * from sys_database;

18 查看表空间

select * from sys_tablespace;

19 查看语言

select * from sys_language;

20 查看角色用户

select * from sys_user; 
select * from sys_shadow; 
select * from sys_roles;

21 查看数据库状态
select * from v$instance; ----database_mode=oracle 模式下才有该视图
22 查看会话进程

select * from sys_stat_activity;

23 查看当前账号会话数

select count(*), usename from sys_stat_activity group by usename;

24 查看系统所有表

SELECT * FROM sys_tables ;

25 查看表字段及注释

select * from information_schema.columns ;
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME ,COLUMN_COMMENT from information_schema.COLUMNS WHERE TABLE_SCHEMA  ='sft_user' ;V008R006C008B0014-mysql下模式
select a.attnum AS “序号”,
c.relname AS “表名”,
cast(obj_description(relfilenode,'pg_class') as varchar) AS “表名描述”,
a.attname AS “列名”,
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as “字段类型”,
d.description AS “备注”
from pg_class c, pg_attribute a , pg_type t, pg_description d
where c.relname = 'selectoptions'
and a.attnum>0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid=a.attrelid
and d.objsubid=a.attnum
ORDER BY c.relname DESC,a.attnum ASC8b0020mysql
SELECT a.attname,a.attoptions,pg_catalog.format_type(a.atttypid, a.atttypmod),CASE WHEN a.attnotnull = 't' THEN '否' ELSE '是' END AS 是否为null,a.attidentity,pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 
(select c.oidfrom pg_catalog.pg_class cleft join pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(ddb_area)$' COLLATE pg_catalog.defaultAND pg_catalog.pg_table_is_visible(c.oid)
AND (c.oid not in (select reloid from sys_recyclebin)) 
)AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

26 查看视图

select * from sys_views where schemaname = 'public'; 
select * from information_schema.views where table_schema = 'public';

27 查看触发器

select * from information_schema.triggers;
--msyql模式
SELECT tgname AS trigger_name,tgrelid::regclass AS table_name,tgfoid::regproc AS function_name,tgtype AS trigger_type,tgenabled AS is_enabled
FROM pg_trigger;

28 查看序列

select * from information_schema.sequences where sequence_schema = 'public';

29 查看约束

select * from sys_constraint where contype = 'p' --u unique,p primary,f foreign,c check,t trigger,x exclusion
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from sys_class a,sys_constraint b where a.oid = b.conrelid and a.relname = 'cc';

30 查看索引

select * from sys_index ;

31 查看表上存在哪些索引

select relname,n.amname as index_type from sys_class m,sys_am n where m.relam = n.oid and m.oid in ( select b.indexrelid from sys_class a,sys_index b where a.oid = b.indrelid and a.relname = 'cc');

32 查看表上存在索引的及大小

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM sys_class c, sys_class c2, sys_index i WHERE c.relname = 'cc' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
ORDER BY c2.relname;

33 查看索引定义，如表 cc 的索引

select b.indexrelid from sys_class a,sys_index b where a.oid = b.indrelid and a.relname = 'cc';

34 查看过程函数定义,如查看 oid=24610 的函数定义

select oid,* from sys_proc where proname = 'insert_platform_action_exist'; --oid =24610
select * from sys_get_functiondef(24610);

35 查看表大小(不含索引等信息)，如查看 exam 表的大小；

select sys_relation_size('exam'); select pg_size_pretty(pg_relation_size('exam'));
--查询库下表大小，并排序
select relname as "tablename", (sys_table_size(oid) / 1024/1024) as "size(MB)" from sys_class where relname like'kingbase%' and relkind ='r' order by 2 desc;

36 查看 DB 大小,如查看 test 库的大小

select sys_size_pretty(sys_database_size('test'));	--12M
--查看所有数据库大小
select sys_database.datname, sys_size_pretty
(sys_database_size(sys_database.datname)) AS size from sys_database;

37 查看数据库大小，等同于 \l+

SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access
privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname,
'CONNECT')
THEN
pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as
"Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

38 查看库下对应模式的大小

SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as "disk space", round((sum(table_size) / pg_database_size(current_database()))
* 100,2) as "percent(%)"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_total_relation_size(pg_catalog.pg_class.oid) as
table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;

39 查看服务器 DB 运行状态

[kingbase@db1 ~]$ sys_ctl status -D $KINGBASE_DATA sys_ctl: server is running (PID: 1038)
/home/kingbase/KingbaseES/V8/Server/bin/kingbase "-D"
"/home/kingbase/KingbaseES/V8/data" [kingbase@db1 ~]$

40 查看每个 DB 的使用情况(读,写,缓存,更新,事务等)

select * from sys_stat_database;

41 查看索引的使用情况

select * from sys_stat_user_indexes;

42 查看表所对应的数据文件路径与大小

SELECT sys_relation_filepath(oid), relpages "大小 KB",* FROM sys_class ;

43 查看索引与相关字段及大小

SELECT n.nspname AS schema_name,
r.rolname as table_owner,
bc.relname AS table_name, ic.relname AS index_name,a.attname	AS column_name,
bc.relpages*8 as index_size_kb
FROM sys_namespace n,sys_class bc,	-- base classsys_class ic,	-- index class
sys_index i,sys_attribute a,	-- att in base
sys_roles r
WHERE bc.relnamespace = n.oid and i.indrelid = bc.oid and i.indexrelid = ic.oid and bc.relowner = r.oid and i.indkey[0] = a.attnum and i.indnatts = 1 and a.attrelid = bc.oid
and n.nspname = 'public' and bc.relname = 'cc'
ORDER BY schema_name, table_name, index_name, attname;

44 查看 Kingbase 当前锁

select * from sys_locks;

备注：relpages*8 是实际所占磁盘大小
45 查看表空间大小

SELECT tbs.spcname,
pg_size_pretty(pg_tablespace_size(tbs.spcname)) AS size
FROM pg_tablespace tbs;

46 查看序列与表的对应关系

WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,
c.relkind, c.relname AS relation
FROM sys_class c JOIN sys_namespace n ON n.oid =
c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables	AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
'->' as depends,
t.fqname AS table
FROM
sys_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
WHERE
d.deptype = 'a' and t.fqname = 'exam';

47 查找锁表的pid

select pid from sys_locks l join sys_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'lockedtable';

48 查找锁表的语句

selectpid,state,usename,query,query_startfromsys_stat_activitywhere pid in ( select pid from sys_locks l join sys_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'lockedtable'); ---查找所有活动的被锁的表
select pid, state, usename, query, query_start
from sys_stat_activity where pid in (
select pid from sys_locks l join sys_class t on l.relation = t.oid and t.relkind = 'r'
);--查看锁表等待的sql
SELECT blocked_locks.pid     AS blocked_pid,blocked_activity.usename  AS blocked_user,blocking_locks.pid     AS blocking_pid,blocking_activity.usename AS blocking_user,blocked_activity.query    AS blocked_statement,blocking_activity.query   AS current_statement_in_blocking_process,blocked_locks.mode ,blocked_locks.locktypeFROM  pg_catalog.pg_locks         blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks         blocking_locksON blocking_locks.locktype = blocked_locks.locktypeAND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.databaseAND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relationAND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.pageAND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tupleAND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxidAND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionidAND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classidAND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objidAND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubidAND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activityON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

49 会话解锁

SELECT sys_cancel_backend(pid);

50 批量(未排除自己)

select sys_cancel_backend(pid)
from sys_stat_activity where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid and t.relkind = 'r'
);

51 查看数据库表、数据库事务号年龄

select
c.oid::regclass as table_name, greatest(age(c.relfrozenxid),
age(t.relfrozenxid)) as age
from
sys_class c
left join sys_class t on
c.reltoastrelid = t.oid
where
c.relkind in ( 'r', 'm' ); select datname,age(datfrozenxid) from sys_database ; ----查看当前超过5min的事务
select query,state from sys_stat_activity where state<>'idle' and (backend_xidisnotnullorbackend_xminisnotnull)andnow()-xact_start > interval '5 min' order by xact_start;
---另一种写法，查看当前超过1min的事务
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > dsinterval '1 minute' ORDER BY duration DESC;

52 查看数据库保留关键字

select * from (select * from sys_get_keywords())t where word='uid';

53 查看数据库索引等

select A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
from
SYS_AM B left join SYS_CLASS F on
B.OID = F.RELAM left join SYS_STAT_ALL_INDEXES E on
F.OID = E.INDEXRELID left join SYS_INDEX C on E.INDEXRELID = C.INDEXRELID left outer join SYS_DESCRIPTION D on C.INDEXRELID = D.OBJOID, SYS_INDEXES A where A.SCHEMANAME = E.SCHEMANAME and A.TABLENAME = E.RELNAME and A.INDEXNAME = E.INDEXRELNAME;
----and E.SCHEMANAME = 'scott' ----and E.RELNAME = 'emp';

54 创建表及注释字段参考

CREATE TABLE weather ( 
city varchar(80),--城市 
temp_lo int, -- 最低温度 
temp_hi int, -- 最高温度 
prcp real, -- 湿度
date date ---日期
);
comment on column weather.temp_lo is '最低温度'; 
comment on column weather.temp_hi is '最高温度'; 
comment on column weather.prcp is '湿度';

55 修改表字段

ALTER TABLE public.student	ALTER name type varchar(11);
ALTER TABLE public.student	ALTER COLUMN name type varchar(11);

56 获取表名及注释

select relname as
tabname,cast(obj_description(relfilenode,'sys_class') as varchar) as comment from sys_class c
where relkind = 'r' and relname not like 'sys_%' and relname not like 'sql_%' order by relname

—过滤掉分表：
—加条件 and relchecks=0 即可
57 获取字段名、类型、注释、是否为空

SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod)astype,a.attname as name, a.attnotnull as notnull
FROM sys_class as c,sys_attribute as a where c.relname = '表名' and a.attrelid = c.oid and a.attnum>0

58 查询某张表中字段、字段类型、长度、主键、唯一、外键、null

select a.attname as 字段名称,format_type(a.atttypid,a.atttypmod) as 字段类型,
(case
when atttypmod-4>0 then atttypmod-4
else 0 end)字段长度,
(case
when (select count(*) from sys_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N'
end) as 主键,
(case
when (select count(*) from sys_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as U,
(case
when (select count(*) from sys_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as R,
(case
when a.attnotnull=true then 'N' else 'Y'
end) as 不是null,
col_description(a.attrelid,a.attnum) as comment,'XEditText' as control from sys_attribute a
where attstattarget=-1 and attrelid = (select oid from sys_class where relname ='user');

59 查看CPU占用高的SQL（根据top查到的会话id=2323,）

SELECT S.procpid, "START", now()-"START" AS lap, S.current_query FROM
( SELECT backendid, sys_stat_get_backend_pid (S.backendid)
AS procpid, sys_stat_get_backend_activity_start (S.backendid)
AS START,sys_stat_get_backend_activity (S.backendid) AS current_query
FROM (SELECT sys_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' and procpid=2323 ORDER BY lap DESC;

–不需要会话ID

SELECT S.procpid, "START", now()-"START" AS lap, S.current_query FROM
( SELECT backendid, sys_stat_get_backend_pid (S.backendid)
AS procpid, sys_stat_get_backend_activity_start (S.backendid)
AS START,sys_stat_get_backend_activity (S.backendid) AS current_query
FROM (SELECT sys_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' ORDER BY lap DESC;

60 切换数据库日志

select sys_switch_wal(); 
-----R6及之后版本切换数据库wal归档日志 
select sys_switch_xlog();
----R3版本切换数据库wal归档日 
select sys_rotate_logfile(); 
-----切换数据库sys_log系统日志

61 以下SQL中需要在数据库添加扩展配置 ，在kingbase.conf配置文件中

shared_preload_libraries 一项中添加 sys_stat_statements
如：
shared_preload_libraries = 'passwordcheck,sys_stat_statements'

62 查看单次调用最消耗IO SQL top 10

select query from sys_stat_statements order by  (blk_read_time+blk_write_time)/calls desc limit 10;

63 IO 开销总量最大SQL

select query from sys_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;

64 最耗共享内存的SQL

select query from sys_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;

65 创建某个模式下的只读账号

--------创建用户
CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
--设置用户默认开启只读事务
ALTER USER readonly SET default_transaction_read_only = ON;
--将schema中usage权限赋予给readonly用户,访问所有已存在的表 GRANT usage ON SCHEMA xyh TO readonly;
--将schema中表的查询权限赋予给readonly用户,访问所有已存在的表
GRANT SELECT ON ALL tables IN SCHEMA xyh TO readonly;
--未来访问xyh模式下所有新建的表：
ALTER DEFAULT privileges IN SCHEMA xyh GRANT SELECT ON tables TO readonly;

66 创建某个模式下的所有权限

------其中模式为energy_cloud_coal，用户为mkyxxfy_zr
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA energy_cloud_coal TO mkyxxfy_zr ;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA energy_cloud_coal TO mkyxxfy_zr ;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA energy_cloud_coal TO mkyxxfy_zr ;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA energy_cloud_coal TO mkyxxfy_zr ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA energy_cloud_coal TO mkyxxfy_zr ;
GRANT USAGE,CREATE ON SCHEMA energy_cloud_coal TO mkyxxfy_zr ; alter default privileges in schema energy_cloud_coal grant ALL ON tables TO mkyxxfy_zr ;
alter default privileges in schema energy_cloud_coal grant ALL ON SEQUENCES TO mkyxxfy_zr ;
alter default privileges in schema energy_cloud_coal grant ALL ON FUNCTIONS TO mkyxxfy_zr ;

67 常用的统计sql参考

最耗IO SQL，单次调用最耗IO SQL TOP 5
select userid::regrole, dbid, query from sys_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5; 总最耗IO SQL TOP 5
select userid::regrole, dbid, query from sys_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;
最耗时 SQL，单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from sys_stat_statements order by mean_time desc limit 5; 总最耗时 SQL TOP 5
select userid::regrole, dbid, query from sys_stat_statements order by total_time desc limit 5;
响应时间抖动最严重 SQL
select userid::regrole, dbid, query from sys_stat_statements order by stddev_time desc limit 5;
最耗共享内存 SQL
select userid::regrole, dbid, query from sys_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
最耗临时空间 SQL
select userid::regrole, dbid, query from sys_stat_statements order by temp_blks_written desc limit 5;

68 重置统计信息

sys_stat_statements是累积的统计，如果要查看某个时间段的统计，需要打快照用户也可以定期清理历史的统计信息，通过调用如下SQL
select sys_stat_statements_reset();

69 查看表结构的语句

select c.relname AS bm,
a.attname AS 字段名称, format_type ( A.atttypid, A.atttypmod ) as 字段类型,
a.attnotnull as 是否为null,
d.description AS 备注 fromsys_classc,sys_attributea,sys_typet,sys_description d,sys_namespace ns where
ns.nspname ='public' --对应的模式名称
and c.relname = 'abc' and a.attnum>=0 and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid(+)=a.attrelid and d.objsubid(+)=a.attnum AND ns.oid = c.relnamespace
ORDER BY c.relname DESC,a.attnum ASC;

70 查询模式名及模式名下对应的所有表名，所有字段名，字段长度，注释

---用SYSTEM用户执行
SELECT isc.table_catalog AS database_name,table_schema, isc.TABLE_NAME, isc.COLUMN_NAME AS NAME,sd.description,
coalesce(isc.character_maximum_length,numeric_precision,-1) as Length,
CASE WHEN POSITION('VARCHAR' IN isc.UDT_NAME) THEN concat(isc.UDT_NAME,'(',isc.character_maximum_length,')') WHEN 'CHARACTER' = isc.data_type THEN
concat(isc.data_type,'(',isc.character_maximum_length,')') WHEN 'INTEGER' = isc.data_type THEN isc.data_type ELSE
isc.UDT_NAME END DATATYPE FROM information_schema.columns isc
LEFT JOIN sys_class sc ON isc.TABLE_NAME = sc.relname
LEFT JOIN sys_attribute sa ON sc.oid=sa.attrelid AND sa.attname
= isc.COLUMN_NAME
LEFT JOIN sys_type st ON sa.atttypid = st.oid
LEFT JOIN sys_description sd ON sd.objoid= sa.attrelid AND sd.objsubid = sa.attnum
LEFT JOIN sys_constraint scon ON scon.conrelid = sc.oid AND sa.attnum = scon.conkey[1]
WHERE table_schema = 'public' AND isc.table_catalog = 'test' 
AND  isc.TABLE_NAME ='d_test' AND sa.attnum > 0 ;

71 金仓KingbaseES开启归档

步骤一：修改kingbase的配置文件(data目录下kingbase.conf)
wal_level=replica archive_mode =on archive_command ='DATE=`date
+%Y%m%d`;DIR="/home/kingbase/arch/$DATE";(test-d$DIR||mkdir-p$DIR)&& cp %p $DIR/%f'
ps：%p 是指相对路径	%f是指文件名
步骤二：创建归档路径
mkdir	-p /home/kingbase/arch chown -R kingbase:kingbase /home/kingbase/arch
步骤三：重启数据库
sys_ctl restart
步骤四：验证归档是否正常
[kingbase@db1 20210722]$ pwd
/home/kingbase/arch/20210722
[kingbase@db1 20210722]$ ll
总用量 49152	
-rw------- 1 kingbase kingbase 16777216 7月
000000010000000000000004	22 2021
-rw------- 1 kingbase kingbase 16777216 7月
000000010000000000000005	22 2021
-rw------- 1 kingbase kingbase 16777216 7月
000000010000000000000006
[kingbase@db1 20210722]$	22 09:54

72 查看某用户的系统权限

SELECT * FROM sys_roles ;

73 查看某用户的表权限

select * from information_schema.table_privileges ;

74 查看某用户的usage权限

select * from information_schema.usage_privileges ; 

75 查看某用户在存储过程函数的执行权限

select * from information_schema.routine_privileges ;

76 查看某用户在某表的列上的权限

select * from information_schema.column_privileges ;

77 查看当前用户能够访问的数据类型

select * from information_schema.data_type_privileges ;

78 查看用户自定义类型上授予的USAGE权限

select * from information_schema.udt_privileges;

79 定时删除sys_log目录下的日志文件

#!/bin/bash### 设置日志目录路径
log_directory="/data/kingbase/data/sys_log/"
### 设置保留时长（以秒为单位）
retention_period=$((60*60*24*30*3)) ### 3个月的秒数
### 获取当前时间戳
current_time=$(date +%s)
### 遍历日志目录中的文件
for file in "$log_directory"* do
### 检查文件是否是一个普通文件
if [ -f "$file" ]
then
### 获取文件的最后修改时间戳
last_modified=$(stat -c %Y "$file")
### 计算文件的存活时长
age=$((current_time - last_modified))
### 如果文件的存活时长超过保留时长，则删除它
if [ "$age" -ge "$retention_period" ]
then
rm "$file" echo "Deleted old log file: $file"
fi
fi
done

#####################end##################
80 查询参数

select * from sys_settings where name='constraint_exclusion';
select
name ,
setting,
reset_val,
context,
pending_restart --如果配置文件中修改了该值 但需要重启，则为 true ， 否则为 false 。
from sys_settings where name='wal_segment_size';

81 查看脏页率

SELECT CASEWHEN COUNT(1) > 0 THEN1ELSE0ENDFROM pg_catalog.pg_stat_user_tablesWHERE n_dead_tup <> 0AND (n_live_tup + n_dead_tup) >= 1000000AND n_dead_tup * 100 / (n_live_tup + n_dead_tup) >= 30;

82 查看统计信息是否过期

select CASEWHEN COUNT(1) > 0 THEN1ELSE0ENDfrom pg_catalog.pg_stat_user_tables psut, pg_catalog.pg_tables pt
WHERE psut.schemaname = pt.schemanameand psut.relname = pt.tablenameand (now() - greatest(psut.last_analyze, last_autoanalyze) >= interval '2 week')and psut.n_live_tup <> 0and psut.n_mod_since_analyze * 100 / psut.n_live_tup > 10;

83 修改数据库默认表空间以及移动表到新表空间

create tablespace 表空间名 location '目录路径';
alter tablespace 表空间名 owner to 属主用户;
alter tablespace 旧表空间名 rename to 新表空间名;
alter database 数据库名 set tablespace 表空间名;
alter table 表名 set tablespace 表空间;

84 手动清理wal日志

sys_controldata /home/kingbase/KOPS_cluster_24/data/ --查看最新的wal file
sys_archivecleanup -d /home/kingbase/KOPS_cluster_24/data/sys_wal 0000000E0000000700000017

85 新建模式用户和库及修改search_path

test=# create schema sft;
CREATE SCHEMA
test=# create user sft_user with password '12345678ab';
CREATE ROLE
test=# grant all privileges on schetest=# grant all privileges on schema sft to sft_user;
GRANT
test=# create database sft owner  sft_user;
CREATE DATABASEtest=# alter database sft set search_path=sft,"$USER", PUBLIC;
ALTER DATABASE
test=# select sys_reload_conf();
sft=> show search_path ;search_path
----------------------
sft, "$USER", PUBLIC
(1 行记录)

86 杀掉某个库上的所有连接

禁止连接dbu库
alter database dbu with allow_connections = 'false';
--允许连接库
alter database dbu  with allow_connections='true';
--杀死所有dbu库上当前连接
select  sys_terminate_backend(pid)  from  sys_stat_activity where datname = 'dbu';  

87 表空间统计信息视图

CREATE OR REPLACE VIEW v_pscinfo as
SELECT 
oid,
spcname,
(SELECT rolname FROM sys_authid WHERE oid=spcowner) AS spcowner,
spcacl,
spcoptions,
pg_size_pretty(pg_tablespace_size(spcname)) AS spcsize
FROM pg_tablespace;COMMENT ON VIEW v_pscinfo IS '表空间信息';
COMMENT ON COLUMN v_pscinfo.oid IS '行标识符';
COMMENT ON COLUMN v_pscinfo.spcname IS '表空间名';
COMMENT ON COLUMN v_pscinfo.spcowner IS '表空间的拥有者';
COMMENT ON COLUMN v_pscinfo.spcacl IS '访问权限';
COMMENT ON COLUMN v_pscinfo.spcoptions IS '表空间级别的选项';
COMMENT ON COLUMN v_pscinfo.spcsize IS '表空间大小';

88 查询分区信息

SELECT*
FROMpg_inherits
JOIN pg_class parent ONpg_inherits.inhparent = parent.oid
JOIN pg_class child ONpg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ONnmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ONnmsp_child.oid = child.relnamespace
WHEREparent.relkind = 'p'-- 'p' represents a partitioned TABLEAND child.relname LIKE 'tr_cle_sys_check_weight_off_site_%';

89 设置同一个库下不同模式的search_path

ALTER DATABASE your_database_name SET search_path TO public, func_schema, other_schemas;

90 查询死亡元组数量已经清理死亡元组信息

test=# create table t1(id int,name varchar(32));
CREATE TABLE
test=# insert into t1 select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
test=# delete from t1 where id > 5000;
DELETE 95000
test=# SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 't1';95000
test=# vacuum t1;
VACUUM
test=# analyze verbose t1;
信息:  正在分析 "public.t1"
信息:  "t1": scanned 43 of 43 pages, containing 5000 live krows and 0 dead krows; 5000 krows in sample, 5000 estimated total krows
ANALYZE
test=#