ORACLE 10g和11g压缩分区表操作脚本

对于OLAP系统，数据量特别大，而且不能随便扩空间，所以压缩也是比较好的方法，压缩比基本是：1：2

下面是压缩脚本，需要先创建log，记录压缩到哪张表的日志。

下面的例子是 ORACLE  11g的

-- Create table

create table TL_COMPRESS_LOG

(

  OWNNAME  VARCHAR2(30),

  TABNAME  VARCHAR2(30),

  SUBPARNAME VARCHAR2(30),

  TSNAME   VARCHAR2(30),

  BEGTIME  DATE,

  ENDTIME  DATE,

  OLD_MB   NUMBER,

  NEW_MB   NUMBER,

  OLD_ROWS NUMBER,

  NEW_ROWS NUMBER

)

tablespace USERS;

-- Add comments to the table 

comment on table TL_COMPRESS_LOG

  is '压缩日志表';

-- Add comments to the columns 

comment on column TL_COMPRESS_LOG.OWNNAME

  is '属主';

comment on column TL_COMPRESS_LOG.TABNAME

  is '表';

comment on column TL_COMPRESS_LOG.SUBPARNAME

  is '子分区';

comment on column TL_COMPRESS_LOG.TSNAME

  is '表空间';

comment on column TL_COMPRESS_LOG.BEGTIME

  is '压缩开始时间';

comment on column TL_COMPRESS_LOG.ENDTIME

  is '压缩结束时间';

comment on column TL_COMPRESS_LOG.OLD_MB

  is '压缩前大小(MB)';

comment on column TL_COMPRESS_LOG.NEW_MB

  is '压缩后大小(MB)';

comment on column TL_COMPRESS_LOG.OLD_ROWS

  is '压缩前记录数';

comment on column TL_COMPRESS_LOG.NEW_ROWS

  is '压缩后记录数';

 

 

 

 

DECLARE

  VT1 DATE;

  VT2 DATE;

  VM1 NUMBER;

  VM2 NUMBER;

  VR1 NUMBER;

  VR2 NUMBER;

BEGIN

  FOR C IN (SELECT '用户名' OWNNAME,

                   TABLE_NAME TABNAME,

                   PARTITION_NAME PARNAME,

                   SUBPARTITION_NAME SUBPARNAME,

                   TABLESPACE_NAME TSNAME

              FROM USER_TAB_SUBPARTITIONS

             WHERE TABLE_NAME LIKE 'TM_%201208'

               AND COMPRESSION != 'ENABLED'

             ORDER BY TABLE_NAME, PARTITION_NAME ,SUBPARTITION_NAME) LOOP

    VT1 := SYSDATE;

    SELECT MAX(BYTES) / 1024 / 1024

      INTO VM1

      FROM DBA_SEGMENTS

     WHERE OWNER = C.OWNNAME

       AND SEGMENT_NAME = C.TABNAME

       AND PARTITION_NAME = C.SUBPARNAME;

    EXECUTE IMMEDIATE 'select /*+ parallel(t,4) */ count(*) from

' || C.TABNAME || ' subpartition (' || C.SUBPARNAME || ') t'

      INTO VR1;

    INSERT INTO TL_COMPRESS_LOG

    VALUES

      (C.OWNNAME,

       C.TABNAME,

       C.PARNAME,

       C.TSNAME,

       VT1,

       NULL,

       VM1,

       NULL,

       VR1,

       NULL);

    --

    EXECUTE IMMEDIATE 'alter table ' || C.TABNAME || ' move subpartition

' || C.SUBPARNAME || ' compress tablespace ' || C.TSNAME;

    --

    VT2 := SYSDATE;

    SELECT MAX(BYTES) / 1024 / 1024

      INTO VM2

      FROM DBA_SEGMENTS

     WHERE OWNER = C.OWNNAME

       AND SEGMENT_NAME = C.TABNAME

       AND PARTITION_NAME = C.SUBPARNAME;

    EXECUTE IMMEDIATE 'select /*+ parallel(t,4) */ count(*) from

' || C.TABNAME || ' subpartition (' || C.SUBPARNAME || ') t'

      INTO VR2;

    UPDATE TL_COMPRESS_LOG

       SET ENDTIME = VT2, NEW_MB = VM2, NEW_ROWS = VR2

     WHERE OWNNAME = C.OWNNAME

       AND TABNAME = C.TABNAME

       AND SUBPARNAME= C.PARNAME;

  END LOOP;

END;

--由于ORACLE 10g不支持直接对子分区进行压缩，所以需要对主分区进行压缩如下：

--ORACLE 10g

DECLARE

  VT1 DATE;

  VT2 DATE;

  VM1 NUMBER;

  VM2 NUMBER;

  VR1 NUMBER;

  VR2 NUMBER;

BEGIN

  FOR C IN (SELECT 'CTMX' OWNNAME,table_name TABNAME,partition_name PARNAME,

           TABLESPACE_NAME TSNAME FROM 

           user_tab_partitions WHERE table_name LIKE '%201303' 

           AND compression='DISABLED') LOOP

    VT1 := SYSDATE;

    SELECT MAX(BYTES) / 1024 / 1024

      INTO VM1

      FROM DBA_SEGMENTS

     WHERE OWNER = C.OWNNAME

       AND SEGMENT_NAME = C.TABNAME

       AND PARTITION_NAME = C.PARNAME;

    EXECUTE IMMEDIATE 'select /*+ parallel(t,4) */ count(*) from

' || C.TABNAME || ' partition (' || C.PARNAME || ') t'

      INTO VR1;

    INSERT INTO TL_COMPRESS_LOG

    VALUES

      (C.OWNNAME,

       C.TABNAME,

       C.PARNAME,

       C.TSNAME,

       VT1,

       NULL,

       VM1,

       NULL,

       VR1,

       NULL);

    --

    EXECUTE IMMEDIATE 'alter table ' || C.TABNAME || ' move partition  

' || C.PARNAME ||' compress' ;

    --

    VT2 := SYSDATE;

    SELECT MAX(BYTES) / 1024 / 1024

      INTO VM2

      FROM DBA_SEGMENTS

     WHERE OWNER = C.OWNNAME

       AND SEGMENT_NAME = C.TABNAME

       AND PARTITION_NAME = C.PARNAME;

    EXECUTE IMMEDIATE 'select /*+ parallel(t,4) */ count(*) from

' || C.TABNAME || ' partition (' || C.PARNAME || ') t'

      INTO VR2;

    UPDATE TL_COMPRESS_LOG

       SET ENDTIME = VT2, NEW_MB = VM2, NEW_ROWS = VR2

     WHERE OWNNAME = C.OWNNAME

       AND TABNAME = C.TABNAME

       AND SUBPARNAME= C.PARNAME;

  END LOOP;

END;

评论

1 楼 cvics_ailk 2014-03-05  

挺好的，挺实用的，希望楼主多共享一些好东西