oracle 11g压缩分区表,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) */ 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;