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


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