一、背景
在使用虚谷数据库时,想了解虚谷数据库存储情况,比如大表占多少,空表占多少,索引占多少,如果能用一个存储过程展示出来就相当方便了
二、虚谷存储过程
--展示库的存储情况,比如大表比例,比如空表比例,索引比例
CREATE OR REPLACE procedure analyze_db as
declare
large_table_num int;
table_all_nums int;
small_table_nums int;
hava_idx_tb_num int;
v_over_mb int ;
begin
v_over_mb:=1000; -- 可以自定义多大的表为大表,只需要改这里,其他不用改
--大于v_over_mb MB 为大表
for i in (select db_id,db_name from sys_databases) loop
select count(*) into large_table_num from (
select table_name,table_size from (
select d.db_name,s.schema_name,t.table_name,8*count(*) as table_size from sys_schemas s,sys_tables t,sys_gstores g,sys_databases d
where g.obj_id=t.table_id and s.schema_id=t.schema_id and s.db_id=t.db_id and g.db_id=d.db_id and t.db_id=d.db_id and d.db_id=i.db_id
group by t.table_name,s.schema_name,d.db_name order by d.db_name,s.schema_name desc
)
where table_size>v_over_mb);
select count(*) into table_all_nums from sys_tables where db_id=i.db_id;
-- 空表比例
select count(*) into small_table_nums from (
select d.db_name,s.schema_name,t.table_name,8*count(*) as table_size from sys_schemas s,sys_tables t,sys_gstores g,sys_databases d
where g.obj_id=t.table_id and s.schema_id=t.schema_id and s.db_id=t.db_id and g.db_id=d.db_id and t.db_id=d.db_id and d.db_id=i.db_id
group by t.table_name,s.schema_name,d.db_name order by d.db_name,s.schema_name desc
)
where table_size=8 ;
select count(*) into hava_idx_tb_num from (
select table_id,count(*) idx_num from sys_indexes where db_id=i.db_id group by table_id
);
if table_all_nums!=0 then
send_msg('库: '||i.db_name||' 大表(>'|| v_over_mb||'MB)比例: '||substr(large_table_num/table_all_nums,0,4)||'% ,'||'空表比例: '|| substr(small_table_nums/table_all_nums,0,4)||'% ,'|| '索引比例:'|| substr(hava_idx_tb_num/table_all_nums,0,4)||'%');
end if;
end loop;
end analyze_db
三、调用存储过程
call analyze_db()