我需要一些有关如何DBMS_COMPRESSION.GET_COMPRESSION_RATIO
在数据库的所有表上运行该包的帮助,以查看是否值得实施。
这是我单独运行的过程,我需要一种方法来自动化它,这样我就不必逐表手动执行它。
SET SERVEROUTPUT ON
DECLARE
blkcnt_cmp PLS_INTEGER;
blkcnt_uncmp PLS_INTEGER;
row_cmp PLS_INTEGER;
row_uncmp PLS_INTEGER;
cmp_ratio NUMBER;
comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => 'USERS' ,
ownname => 'OWNER' ,
objname => 'TABLE_NAME' ,
subobjname => NULL ,
comptype => DBMS_COMPRESSION.COMP_ADVANCED,
blkcnt_cmp => blkcnt_cmp,
blkcnt_uncmp => blkcnt_uncmp,
row_cmp => row_cmp,
row_uncmp => row_uncmp,
cmp_ratio => cmp_ratio,
comptype_str => comptype_str,
subset_numrows=> DBMS_COMPRESSION.comp_ratio_minrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line( 'Number of blocks used by the compressed sample of the object : ' ||
blkcnt_cmp);
DBMS_OUTPUT.put_line( 'Number of blocks used by the uncompressed sample of the object : ' ||
blkcnt_uncmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in compressed sample of the object : ' ||
row_cmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in uncompressed sample of the object : ' ||
row_uncmp);
DBMS_OUTPUT.put_line ('Estimated Compression Ratio of Sample : ' ||
cmp_ratio);
DBMS_OUTPUT.put_line( 'Compression Type : ' ||
comptype_str);
END;
这就是 PL/SQL 的用途,用于编写代码以编程方式进行调用。查询字典以获取表名,并在循环内调用该过程。像这样的东西:
但是,您可能不想用于
dbms_output
此目的。我建议将结果插入到您为此目的创建的日志表中,以便您稍后可以随意查询。