如何测量Firebird 2.1数据库上的Blob占用的空间量? [英] How can I measure the amount of space taken by blobs on a Firebird 2.1 database?
本文介绍了如何测量Firebird 2.1数据库上的Blob占用的空间量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个使用Firebird 2.1的生产数据库,在这里我需要找出每个表(包括Blob)使用了多少空间.斑点部分是棘手的部分,因为标准统计报告未涵盖该部分.
I have a production database, using Firebird 2.1, where I need to find out how much space is used by each table, including the blobs. The blob-part is the tricky one, because it is not covered using the standard statistical report.
我无法轻松访问服务器的桌面,因此安装UDF等不是一个好的解决方案.
I do not have easy access to the server's desktop, so installing UDFs etc. is not a good solution.
我如何轻松地做到这一点?
How can I do this easily?
推荐答案
您可以使用以下语句来计算数据库中所有BLOB字段的总大小:
You can count total size of all BLOB fields in a database with following statement:
EXECUTE BLOCK RETURNS (BLOB_SIZE BIGINT)
AS
DECLARE VARIABLE RN CHAR(31) CHARACTER SET UNICODE_FSS;
DECLARE VARIABLE FN CHAR(31) CHARACTER SET UNICODE_FSS;
DECLARE VARIABLE S BIGINT;
BEGIN
BLOB_SIZE = 0;
FOR
SELECT r.rdb$relation_name, r.rdb$field_name
FROM rdb$relation_fields r JOIN rdb$fields f
ON r.rdb$field_source = f.rdb$field_name
WHERE f.rdb$field_type = 261
INTO :RN, :FN
DO BEGIN
EXECUTE STATEMENT
'SELECT SUM(OCTET_LENGTH(' || :FN || ')) FROM ' || :RN ||
' WHERE NOT ' || :FN || ' IS NULL'
INTO :S;
BLOB_SIZE = :BLOB_SIZE + COALESCE(:S, 0);
END
SUSPEND;
END
这篇关于如何测量Firebird 2.1数据库上的Blob占用的空间量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文