We are in process of compressing very huge more tha ( 1 TB ) LOB data and we need assistance.
Our main concern in adding LOB clause into subpartition template so that newly added subpartion will be compressed data.
Let me give a brief idea about the scenario
We did couple of test to move lob to compressed lob but that is taking long time and not completing within maintenance window.
So we have decided to enable compression to only newly added subpartitions. so that we would save some disk space in future and old un compressed data would get archived and in some time all data in table would be compressed data.
But i am unable to find a way to add LOB clause to subpartition template. I am not sure if oracle supports this.
Test case for an example.
following is ddl of table with subpartition template.
CREATE TABLE sales
(
sales_name VARCHAR2 (6),
sales_dt DATE,
log_data CLOB
)
LOB (
log_data)
STORE AS
(
TABLESPACE AUSERS
ENABLE STORAGE IN ROW
CHUNK 16384
PCTVERSION 10
NOCACHE NOLOGGING)
PARTITION BY RANGE
(sales_dt)
SUBPARTITION BY LIST
(sales_name)
SUBPARTITION TEMPLATE (
SUBPARTITION a1 VALUES ('true'), SUBPARTITION a2 VALUES ('false'))
(
PARTITION p0701 VALUES LESS THAN (TO_DATE ('2007-02-01', 'yyyy-mm-dd')),
PARTITION p0702 VALUES LESS THAN (TO_DATE ('2007-03-01', 'yyyy-mm-dd')));
Now i want to modify subpartition template so that new subpartitions will be compressed one.
i tried below
ALTER TABLE sales SET SUBPARTITION TEMPLATE (
SUBPARTITION a1 VALUES ('true') LOB (log_data ) STORE AS ( COMPRESS HIGH ),
SUBPARTITION a2 VALUES ('false') LOB (log_data) STORE AS ( COMPRESS HIGH));
but it throws an error ORA-14605.
Kindly advise how can we do this or any advise you have on this matter.