Hi all,
I have a table which is interval partitioned and has lob subpartitions.
(Table definition attached)
Is there a way to move and HCC the lob subpartitions
- Move for non-lobs and compress works
ALTER TABLE show_lob_storage MOVE SUBPARTITION SYS_SUBP2116124 TABLESPACE
pds_data COMPRESS FOR ARCHIVE HIGH ONLINE;
Table SHOW_LOB_STORAGE altered.
- Move without compression works
alter table show_lob_storage move subpartition SYS_SUBP2116124 lob
(additionalinformation) store as securefile (tablespace pds_data) online;
Table SHOW_LOB_STORAGE altered.
- Move for LOBS and compress does not seem to work
SQL> alter table show_lob_storage move subpartition SYS_SUBP2116124 lob
(additionalinformation) compress high online;
alter table show_lob_storage move subpartition SYS_SUBP2116124 lob
(additionalinformation) compress high online
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> alter table show_lob_storage move subpartition SYS_SUBP2116124 lob
(additionalinformation) store as securefile (tablespace pds_data) compress
high online;
alter table show_lob_storage move subpartition SYS_SUBP2116124 lob
(additionalinformation) store as securefile (tablespace pds_data) compress
high online
ERROR at line 1:
ORA-14160: this physical attribute may not be specified for a table
subpartition
--
Fairlie Rego
Executive Database Architect
www.technoconsulting.com.au
http://www.linkedin.com/in/fairlierego
https://fairlierego.wordpress.com/
Twitter @fairlierego
CREATE TABLE "XXX_OWNER"."SHOW_LOB_STORAGE"
( "INTERCHANGEKEY" NUMBER(38,0) NOT NULL ENABLE,
"PURGEDATE" DATE NOT NULL ENABLE,
"ADDITIONALINFORMATION" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "XXX_DATA"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
PARTITION BY RANGE ("PURGEDATE") INTERVAL (NUMTODSINTERVAL('1', 'DAY'))
SUBPARTITION BY HASH ("INTERCHANGEKEY")
SUBPARTITIONS 16
(PARTITION "PAYM_INTCHG_INIT_PART" VALUES LESS THAN (TO_DATE(' 2017-11-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "XXX_DATA"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
( SUBPARTITION "SYS_SUBP2116124"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
COLUMN STORE COMPRESS FOR ARCHIVE HIGH NO ROW LEVEL LOCKING ,
SUBPARTITION "SYS_SUBP2116125"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116126"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116127"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116128"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116129"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116130"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116131"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116132"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116133"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116134"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116135"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116136"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116137"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116138"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ,
SUBPARTITION "SYS_SUBP2116139"
LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
TABLESPACE "XXX_DATA" )
TABLESPACE "XXX_DATA"
NOCOMPRESS ) ) ENABLE ROW MOVEMENT ;
