新建数据kingbase及kingbase模式

CREATE DATABASE kingbase OWNER kingbase;CREATE SCHEMA kingbase AUTHORIZATION "kingbase";

在数据库kingbase的kingbase模式下新建两张测试表test_size,test_size1并插入数据

CREATE TABLE "kingbase"."test_szie" ("id" integer AUTO_INCREMENT,"name" character varying(8 char) NOT NULL,"create_time" timestamp without time zone NOT NULL,"update_time" timestamp without time zone NOT NULL,"in_number" integer NULL,"number" integer NULL,CONSTRAINT "con_kingbase_test_szie_id" PRIMARY KEY (id));ALTER TABLE "kingbase".test_szie SETTABLESPACE "sys_default";INSERT INTO "kingbase"."test_szie" ("number","name", "create_time", "update_time", "in_number")VALUES(generate_series(1,99999), '张三',now(), now(), random());CREATE TABLE "kingbase"."test_szie1" ("id" integer AUTO_INCREMENT,"name" character varying(8 char) NOT NULL,"create_time" timestamp without time zone NOT NULL,"update_time" timestamp without time zone NOT NULL,"in_number" integer NULL,"number" integer NULL,CONSTRAINT "con_kingbase_test_szie_id" PRIMARY KEY (id));ALTER TABLE "kingbase".test_szie1 SETTABLESPACE "sys_default";ALTER TABLE "kingbase".test_szie1 SETTABLESPACE "sys_default";INSERT INTO "kingbase"."test_szie1" ("number","name", "create_time", "update_time", "in_number")VALUES(generate_series(1,99999), '张三',now(), now(), random());

查询数据库大小

select sys_database_size('kingbase')

select sys_size_pretty(sys_database_size('kingbase'));

查询所有数据库大小

select sys_database.datname, sys_database_size(sys_database.datname) as size from sys_database order by size desc;

查询kingbase模式大小

SELECT sys_size_pretty(sum(table_size)::bigint) as "disk space",sum(table_size)::bigint as "total size"FROM ( SELECT sys_catalog.sys_namespace.nspname as schema_name, sys_total_relation_size(sys_catalog.sys_class.oid) as table_size FROMsys_catalog.sys_classJOIN sys_catalog.sys_namespaceON relnamespace = sys_catalog.sys_namespace.oid WHERE sys_catalog.sys_namespace.nspname = 'kingbase') t

查询库下所有模式大小

SELECT schema_name, sys_size_pretty(sum(table_size)::bigint) as "disk space",sum(table_size)::bigint as "total size"FROM ( SELECT sys_catalog.sys_namespace.nspname as schema_name, sys_total_relation_size(sys_catalog.sys_class.oid) as table_size FROMsys_catalog.sys_classJOIN sys_catalog.sys_namespaceON relnamespace = sys_catalog.sys_namespace.oid WHERE sys_catalog.sys_namespace.nspname NOT IN ('information_schema','src_restrict','anon','dbms_sql','xlog_record_read','pg_catalog','pg_bitmapindex','sys_catalog','sysaudit','sysmac','sys')) tGROUP BY schema_name

查询单表大小

select sys_size_pretty(sys_relation_size('kingbase.test_szie'));

查询模式下所有表大小

SELECTtable_name,sys_size_pretty(table_size) AS table_size,sys_size_pretty(indexes_size) AS indexes_size,sys_size_pretty(total_size) AS total_sizeFROM (SELECTtable_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_sizeFROM (SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_nameFROM information_schema.TABLES WHERE table_schema ='kingbase') AS all_tablesORDER BY total_size DESC) AS pretty_sizes