KingBase 查询数据数据总量、条数、只读账号、表大小、不常用操作等

KingBase V8 中需要用到的操作，记录一下。

1.KingBase 查询数据库数据条数

select
	sum(t.n_live_tup) as 行数
from pg_stat_user_tables t
where t.schemaname = '模式名';

2.查看当前连接信息

select * from sys_stat_activity;

3.创建只读账号

CREATE USER only_read WITH ENCRYPTED PASSWORD '123456';

alter user only_read set default_transaction_read_only=on;

GRANT USAGE ON SCHEMA public to only_read;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO only_read;

GRANT CONNECT ON DATABASE prod to only_read;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO only_read;

4.查询数据库中表大小

SELECT
	table_name,
	sys_size_pretty(table_size) AS table_size,
	sys_size_pretty(indexes_size) AS indexes_size,
	sys_size_pretty(total_size) AS total_size
FROM (
SELECT
	table_name,
	sys_table_size(table_name) AS table_size,
	sys_indexes_size(table_name) AS indexes_size,
	sys_total_relation_size(table_name) AS total_size
FROM (
	SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
	FROM information_schema.TABLES WHERE table_schema ='模式名'
	) AS all_tables
	ORDER BY total_size DESC
	) AS pretty_sizes;

5.查询数据库总量

库总量
select sys_size_pretty(sys_database_size('库名'));

表空间大小
select sys_size_pretty(sys_tablespace_size('sys_default'));