Oracle 表空间、自增分区分区表压缩-每日运维

Oracle 表空间、自增分区分区表压缩

– 创建压缩表空间

create tablespace compress_data_tbs DATAFILE 'XXX' size 100m autoextend on next 128m maxsize unlimited default COMPRESS FOR OLTP;

– omf 管理，可不指定名称

create tablespace compress_data_tbs DATAFILE size 100m autoextend on next 128m maxsize unlimited default COMPRESS FOR OLTP NOLOGGING;

– nologging 设置不记录日志，需要关闭DB级force logging

alter database no force logging;

– 删除表空间及数据文件

drop tablespace compress_data_tbs including contents and datafiles;

– 创建压缩分区表（按月自动分区），如果是使用的压缩表空间，可不指定compress参数

DROP TABLE SCOTT.TEST_COMPRESS_PARTTAB;


CREATE TABLE SCOTT.TEST_COMPRESS_PARTTAB

(

    COL1            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL

  , COL2            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL

  , COL3            VARCHAR2(10 BYTE) DEFAULT (' ') NOT NULL

  , TIME            VARCHAR2(17 BYTE) DEFAULT (' ') NOT NULL

  , COL5            VARCHAR2(50 BYTE) DEFAULT (' ') NOT NULL

  , COL6            VARCHAR2(25 BYTE) DEFAULT (' ') NOT NULL

  , COL7            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL

  , COL8            VARCHAR2(30 BYTE) DEFAULT (' ') NOT NULL

  , COL9            VARCHAR2(30 BYTE) DEFAULT (' ') NOT NULL

  , COL10           VARCHAR2(4000 BYTE) DEFAULT (' ') NOT NULL

  , CLOB_COL        CLOB DEFAULT (' ') NOT NULL

  , PARTITION_DATE  TIMESTAMP(3) GENERATED ALWAYS AS(TO_TIMESTAMP(TIME, 'yyyymmddhh24missff3'))

)

LOB (CLOB_COL) STORE AS SECUREFILE (TABLESPACE DATA_TS ENABLE STORAGE IN ROW COMPRESS)

PARTITION BY RANGE(PARTITION_DATE) INTERVAL ( NUMTOYMINTERVAL(1, 'MONTH') ) STORE IN (DATA_TS,IDX_TS)

    (   PARTITION PART_202201 VALUES LESS THAN (TO_DATE('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202202 VALUES LESS THAN (TO_DATE('2022-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202203 VALUES LESS THAN (TO_DATE('2022-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202204 VALUES LESS THAN (TO_DATE('2022-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202205 VALUES LESS THAN (TO_DATE('2022-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202206 VALUES LESS THAN (TO_DATE('2022-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202207 VALUES LESS THAN (TO_DATE('2022-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202208 VALUES LESS THAN (TO_DATE('2022-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202209 VALUES LESS THAN (TO_DATE('2022-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202210 VALUES LESS THAN (TO_DATE('2022-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202211 VALUES LESS THAN (TO_DATE('2022-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202212 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202301 VALUES LESS THAN (TO_DATE('2023-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202302 VALUES LESS THAN (TO_DATE('2023-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202303 VALUES LESS THAN (TO_DATE('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202304 VALUES LESS THAN (TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202305 VALUES LESS THAN (TO_DATE('2023-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202306 VALUES LESS THAN (TO_DATE('2023-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202307 VALUES LESS THAN (TO_DATE('2023-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202308 VALUES LESS THAN (TO_DATE('2023-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202309 VALUES LESS THAN (TO_DATE('2023-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202310 VALUES LESS THAN (TO_DATE('2023-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS

      , PARTITION PART_202311 VALUES LESS THAN (TO_DATE('2023-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS)

PCTFREE 1

COMPRESS FOR OLTP

TABLESPACE DATA_TS

NOCACHE

ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX SCOTT.TEST_COMPRESS_PARTTAB_PK ON SCOTT.TEST_COMPRESS_PARTTAB(COL1, COL2, COL3, TIME, COL5, COL6, COL7) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1;

ALTER TABLE SCOTT.TEST_COMPRESS_PARTTAB ADD(CONSTRAINT TEST_COMPRESS_PARTTAB_PK PRIMARY KEY(COL1, COL2, COL3, TIME, COL5, COL6, COL7) USING INDEX SCOTT.TEST_COMPRESS_PARTTAB_PK ENABLE VALIDATE);

CREATE INDEX SCOTT.TEST_COMPRESS_PARTTAB_IDX_01 ON SCOTT.TEST_COMPRESS_PARTTAB(COL1, COL2, TIME) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1 LOCAL;

CREATE INDEX SCOTT.IDX_TEST_COMPRESS_PARTTAB_TIME ON SCOTT.TEST_COMPRESS_PARTTAB(TIME) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1 LOCAL;

Oracle 表空间、自增分区分区表压缩