ORACLE 10g and 11g compressed Partition Table operation script

ORACLE 10g and 11g compression Partition Table operation scripts for OLAP systems, the data volume is very large, and can not expand the space at will, so compression is also a good method, the compression ratio is basically: 1: 2. The following is a compression script. You must first create a log to record the logs of the table to which the logs are compressed. The following example is the ORACLE 11g -- Create tablecreate 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 'compressed log table '; -- Add comments to the columns comment on column TL_COMPRESS_LOG.OW NNAME is 'main'; comment on column TL_COMPRESS_LOG.TABNAME is 'table'; comment on column TL_COMPRESS_LOG.SUBPARNAME is 'subpartition'; comment on column TL_COMPRESS_LOG.TSNAME is 'tablespaces '; comment on column TL_COMPRESS_LOG.BEGTIME is 'Compression start time'; comment on column TL_COMPRESS_LOG.ENDTIME is 'Compression End Time'; comment on column TL_COMPRESS_LOG.OLD_MB is 'pre-compression size (MB )'; comment on column TL_COMPRESS_LOG.NEW_MB is 'compressed size (MB) '; comment on co Lumn TL_COMPRESS_LOG.OLD_ROWS is 'number of records before compression '; comment on column TL_COMPRESS_LOG.NEW_ROWS is 'number of compressed records'; DECLARE VT1 DATE; VT2 DATE; VM1 NUMBER; VM2 NUMBER; VR1 NUMBER; VR2 NUMBER; BEGIN FOR C IN! = '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; -- since ORACLE 10 Gb does not support direct compression of subpartitions, compression of the primary partition is required as follows: -- ORACLE 10 gDECLARE 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 '% 8080' AND compression = 'Disabled') LOOP VT1: = SYSDATE; select max (BYTES)/201303 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 | 'companys'; -- 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;