【KingbaseES】数据库如何查询数据库，模式及表大小-Toy模板网

这篇具有很好参考价值的文章主要介绍了【KingbaseES】数据库如何查询数据库，模式及表大小。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

新建数据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 SET
TABLESPACE "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 SET
TABLESPACE "sys_default";


ALTER TABLE "kingbase".test_szie1 SET
TABLESPACE "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')

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

select sys_size_pretty(sys_database_size('kingbase'));

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

查询所有数据库大小

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

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

查询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
     FROM  sys_catalog.sys_class 
         JOIN sys_catalog.sys_namespace 
             ON relnamespace = sys_catalog.sys_namespace.oid
     WHERE sys_catalog.sys_namespace.nspname = 'kingbase'
) t

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

查询库下所有模式大小

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
     FROM  sys_catalog.sys_class 
         JOIN sys_catalog.sys_namespace 
             ON 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')
) t
GROUP BY schema_name

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

查询单表大小

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

【KingbaseES】数据库如何查询数据库，模式及表大小,金仓数据库KingbaseES,KingbaseESR6,KingbaseESR3,数据库,sql,kingbase

查询模式下所有表大小

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 ='kingbase'
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes