kingbase-日常维护

查看用户连接信息

select datname,usename,client_addr,client_port,pid,state from sys_stat_activity;

select connections();

查看锁表监控

select database,relation,sc.relname,pid,mode,granted from sys_locks sl
left join sys_database sd on sl.database=sd.oid
left join sys_class sc on sl.relation=sc.oid
where sys_backend_pid()<>sl.pid and granted='f';

杀掉锁信息

select sys_terminate_backend(2342) from sys_stat_activity;

查看数据库IO统计信息

select * from sys_stat_database;

查看test 数据库大小，单位字节。

select sys_database_size('test');

查看所有数据库大小

select datname,pg_size_pretty(pg_database_size(datname)) as size from pg_database;

查看单个表大小

select pg_size_pretty(pg_relation_size('t01')) as size;

查看单个表总大小，包括索引大小

select pg_size_pretty(pg_total_relation_size('t01')) as size;

查看所有表大小

select relname,pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables;

查看所有表总大小，包括索引大小

select relname,pg_size_pretty(pg_total_relation_size('t01')) as size from pg_stat_user_tables;

查看单个索引大小

select pg_size_pretty(pg_relation_size('t01')) as size;

查看所有表空间大小

select spcname,pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;
或
select spcname,pg_size_pretty(pg_tablespace_size(oid)) as size from pg_tablespace;

检查无效的数据库对象

select owner,object_name,object_type from dba_objects where status='invalid';