人大金仓KingBaseV8常用语句（持续更新）

目录

1.查询锁表语句

2.查询正在运行的sql

3.查询运行时间

4.查询参数

5.查询对象id

6.根据ID查询对象大小

7.查询所有系统表

8.查询KES系统视图

9.查询兼容ORACLE的系统视图

10.查看授权文件目录，授权文件有效期

11.查询会话数

12.脏页率

13.统计信息过期

14.命令行执行sql文件

15.手动切换日志

16.造数据-测试数据

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

18.V8R3复制槽添加和删除

19.重新拉取备库

---

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