对表数据压缩compress的修改和查询

#1 将表转换为basic压缩表

alter table emp move compress basic;

#2 将表转换为OLTP压缩表

alter table emp move compress for oltp;

#3 将一个分区转换为basic压缩表

alter table table_name move partitoin partition_name compress basic;

#4 将一个分区转换为oltp压缩表

alter table table_name move partitoin partition_name compress for oltp;

#5 修改一个分区的现有压缩算法，修改之后只对新记录有效

alter table table_name modify partition,partiton_name compress for oltp;

#查看记录是压缩记录

select decode(

    DBMS_COMPRESSION.GET_COMPRESSION_TYPE(

        OWNNAME => 'SCOTT',

        TABNAME => 'EMP',

        ROW_ID => 'AAAVVhAAEAAAACzAAC'),

    1,'No Compression',

    2,'Basic or OLTP Compression for Query High',

    3,'Hybrid Columnar Compression for Query Low',

    4,'Hybrid Columnar Compression for Archive High',

    5,'Hybrid Columnar Compression for Archive Low',

    'Unknown Compression Type') compression_type

from dual;

SQL> desc dbms_compression PROCEDURE GET_COMPRESSION_RATIO Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SCRATCHTBSNAME                 VARCHAR2                IN OWNNAME                        VARCHAR2                IN TABNAME                        VARCHAR2                IN PARTNAME                       VARCHAR2                IN COMPTYPE                       NUMBER                  IN BLKCNT_CMP                     BINARY_INTEGER          OUT BLKCNT_UNCMP                   BINARY_INTEGER          OUT ROW_CMP                        BINARY_INTEGER          OUT ROW_UNCMP                      BINARY_INTEGER          OUT CMP_RATIO                      NUMBER                  OUT COMPTYPE_STR                   VARCHAR2                OUT SUBSET_NUMROWS                 NUMBER                  IN     DEFAULT FUNCTION GET_COMPRESSION_TYPE RETURNS NUMBER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME                        VARCHAR2                IN TABNAME                        VARCHAR2                IN ROW_ID                         ROWID                   IN PROCEDURE INCREMENTAL_COMPRESS Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME                        VARCHAR2(30)            IN TABNAME                        VARCHAR2(128)           IN PARTNAME                       VARCHAR2(30)            IN COLNAME                        VARCHAR2                IN DUMP_ON                        NUMBER                  IN     DEFAULT AUTOCOMPRESS_ON                NUMBER                  IN     DEFAULT WHERE_CLAUSE                   VARCHAR2                IN     DEFAULT

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2140691/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28572479/viewspace-2140691/