目录
1.查询锁表语句
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.locktype
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
2.查询正在运行的sql
select datname,usename,query,client_port, client_addr from sys_stat_activity;
3.查询运行时间
select datname,usename,query,current_timestamp - query_start as runtiem ,client_port, client_addr from sys_stat_activity where state not like 'idle%';
4.查询参数
select * from sys_settings where name='constraint_exclusion';
select
name ,
setting,
reset_val,
context,--是否需要重启数据库生效,f--不需要,t--需要
pending_restart --是否重新加载
from sys_settings where name='wal_segment_size';
5.查询对象id
select oid,relname,relfilenode,reltoastrelid from sys_class where relname ='t01';
6.根据ID查询对象大小
select sys_size_pretty(sys_relation_size(27171));
7.查询所有系统表
select relkind ,relname from sys_class where relnamespace = (select oid from sys_namespace
where nspname ='pg_catalog')
and relkind ='r'
order by 1,2;
8.查询KES系统视图
select relkind ,relname from sys_class where relnamespace = (
select oid from sys_namespace
where nspname ='sys_catalog')
and relkind ='v'
order by 1,2;
9.查询兼容ORACLE的系统视图
select relkind ,relname from sys_class where relnamespace = (
select oid from sys_namespace
where nspname ='sys')
and relkind ='v'
order by 1,2;
10.查看授权文件目录,授权文件有效期
find /home/kingbase/ -name license.dat
ksql -Usystem -dtest -c 'SELECT GET_LICENSE_VALIDDAYS()'
11.查询会话数
SELECT COUNT(*) AS session_count FROM pg_stat_activity;
12.脏页率
SELECT CASE
WHEN COUNT(1) > 0 THEN
1
ELSE
0
END
FROM pg_catalog.pg_stat_user_tables
WHERE n_dead_tup <> 0
AND (n_live_tup + n_dead_tup) >= 1000000
AND n_dead_tup * 100 / (n_live_tup + n_dead_tup) >= 30;
13.统计信息过期
select CASE
WHEN COUNT(1) > 0 THEN
1
ELSE
0
END
from pg_catalog.pg_stat_user_tables psut, pg_catalog.pg_tables pt
WHERE psut.schemaname = pt.schemaname
and psut.relname = pt.tablename
and (now() - greatest(psut.last_analyze, last_autoanalyze) >= interval '2 week')
and psut.n_live_tup <> 0
and psut.n_mod_since_analyze * 100 / psut.n_live_tup > 10;
14.命令行执行sql文件
ksql -U kmonitor -d kops -W 12345678ab -f /home/kingbase/kops_init.sql
test=# \i /home/kingbase/test.sql
15.手动切换日志
test=# select sys_switch_wal();
sys_switch_wal
----------------
0/8000070
(1 行记录)
16.造数据-测试数据
# 随机数函数
CREATE OR REPLACE FUNCTION RANDOM_NUMBER(INTEGER, INTEGER)
RETURNS INTEGER AS
$BODY$
DECLARE START_INT ALIAS FOR $1;
END_INT ALIAS FOR $2; BEGIN RETURN trunc
(random() * (END_INT - START_INT + 1) + START_INT); END;
$BODY$
LANGUAGE plpgsql;
# 随机日期函数
CREATE OR REPLACE FUNCTION RANDOM_DAYS(START_DATE DATE, END_DATE DATE)
RETURNS DATE AS
$BODY$
DECLARE INTERVAL_DAYS INTEGER; RANDOM_DAYS INTEGER;
RANDOM_DATE DATE; BEGIN INTERVAL_DAYS := END_DATE - START_DATE;
RANDOM_DAYS := RANDOM_NUMBER(0, INTERVAL_DAYS);
RANDOM_DATE := START_DATE + RANDOM_DAYS;
RETURN RANDOM_DATE; END;
$BODY$
LANGUAGE plpgsql;
create table tb(id bigint,stat date,no bigint,pdate date,info varchar2(50));
test=# insert into tb select generate_series(1,1000000),clock_timestamp(),RANDOM_NUMBER(1,100000000),RANDOM_DAYS('2019-01-01'::date,'2019-05-31'::date),md5(random()::text) from dual;
17.修改数据库默认表空间以及移动表到新表空间
create tablespace 表空间名 location '目录路径';
alter tablespace 表空间名 owner to 属主用户;
alter tablespace 旧表空间名 rename to 新表空间名;
alter database 数据库名 set tablespace 表空间名;
alter table 表名 set tablespace 表空间;
18.V8R3复制槽添加和删除
select sys_create_physical_replication_slot('$SLOT_NAME');
select sys_drop_physical_replication_slot('$SLOT_NAME');
19.重新拉取备库
/opt/Kingbase/ES/V8/Server/bin/sys_basebackup -D /opt/Kingbase/ES/V8/data -h 主机IP -U SYSTEM -W 密码 -p 54321 -X stream -F p -P