Hi, on 19.9 (don't remember, in which release the online modification
enhancements are introduced, probably 12.2 ) this syntax works (i've
modified the tablespace from your script for obvious reason)
SQL>
SQL> select table_name,partition_name, subpartition_name, compress_for from
user_tab_subpartitions;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBPARTITION_NAME
COMPRESS_FOR
--------------------------------------------------------------------------------------------------------------------------------
------------------------------
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116124
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116125
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116126
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116127
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116128
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116129
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116130
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116131
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116132
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116133
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116134
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116135
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116136
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116137
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116138
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116139
16 rows selected.
SQL>
SQL> alter TABLE "SHOW_LOB_STORAGE" modify
2 PARTITION BY RANGE ("PURGEDATE") INTERVAL (NUMTODSINTERVAL('1',
'DAY'))
3 SUBPARTITION BY HASH ("INTERCHANGEKEY")
4 SUBPARTITIONS 16
5 (PARTITION "PAYM_INTCHG_INIT_PART" VALUES LESS THAN (TO_DATE('
2017-11-01
6 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
7 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
8 STORAGE(
9 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
10 TABLESPACE "USERS"
11 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
12 ENABLE STORAGE IN ROW CHUNK 8192
13 CACHE compress high KEEP_DUPLICATES
14 STORAGE(
15 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
16 ( SUBPARTITION "SYS_SUBP2116124"
17 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
18 TABLESPACE "USERS" )
19 TABLESPACE "USERS"
20 COLUMN STORE COMPRESS FOR ARCHIVE HIGH NO ROW LEVEL LOCKING ,
21 SUBPARTITION "SYS_SUBP2116125"
22 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
23 TABLESPACE "USERS" )
24 TABLESPACE "USERS"
25 COMPRESS FOR ARCHIVE HIGH ,
26 SUBPARTITION "SYS_SUBP2116126"
27 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
28 TABLESPACE "USERS" )
29 TABLESPACE "USERS"
30 COMPRESS FOR ARCHIVE HIGH ,
31 SUBPARTITION "SYS_SUBP2116127"
32 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
33 TABLESPACE "USERS" )
34 TABLESPACE "USERS"
35 COMPRESS FOR ARCHIVE HIGH ,
36 SUBPARTITION "SYS_SUBP2116128"
37 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
38 TABLESPACE "USERS" )
39 TABLESPACE "USERS"
40 COMPRESS FOR ARCHIVE HIGH ,
41 SUBPARTITION "SYS_SUBP2116129"
42 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
43 TABLESPACE "USERS" )
44 TABLESPACE "USERS"
45 COMPRESS FOR ARCHIVE HIGH ,
46 SUBPARTITION "SYS_SUBP2116130"
47 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
48 TABLESPACE "USERS" )
49 TABLESPACE "USERS"
50 COMPRESS FOR ARCHIVE HIGH ,
51 SUBPARTITION "SYS_SUBP2116131"
52 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
53 TABLESPACE "USERS" )
54 TABLESPACE "USERS"
55 COMPRESS FOR ARCHIVE HIGH ,
56 SUBPARTITION "SYS_SUBP2116132"
57 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
58 TABLESPACE "USERS" )
59 TABLESPACE "USERS"
60 COMPRESS FOR ARCHIVE HIGH ,
61 SUBPARTITION "SYS_SUBP2116133"
62 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
63 TABLESPACE "USERS" )
64 TABLESPACE "USERS"
65 COMPRESS FOR ARCHIVE HIGH ,
66 SUBPARTITION "SYS_SUBP2116134"
67 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
68 TABLESPACE "USERS" )
69 TABLESPACE "USERS"
70 COMPRESS FOR ARCHIVE HIGH ,
71 SUBPARTITION "SYS_SUBP2116135"
72 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
73 TABLESPACE "USERS" )
74 TABLESPACE "USERS"
75 COMPRESS FOR ARCHIVE HIGH ,
76 SUBPARTITION "SYS_SUBP2116136"
77 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
78 TABLESPACE "USERS" )
79 TABLESPACE "USERS"
80 COMPRESS FOR ARCHIVE HIGH ,
81 SUBPARTITION "SYS_SUBP2116137"
82 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
83 TABLESPACE "USERS" )
84 TABLESPACE "USERS"
85 COMPRESS FOR ARCHIVE HIGH ,
86 SUBPARTITION "SYS_SUBP2116138"
87 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
88 TABLESPACE "USERS" )
89 TABLESPACE "USERS"
90 COMPRESS FOR ARCHIVE HIGH ,
91 SUBPARTITION "SYS_SUBP2116139"
92 LOB ("ADDITIONALINFORMATION") STORE AS SECUREFILE (
93 TABLESPACE "USERS" )
94 TABLESPACE "USERS"
95 COMPRESS FOR ARCHIVE HIGH ) ) ENABLE ROW MOVEMENT online;
Table altered.
SQL>
SQL> select table_name,partition_name, subpartition_name, compress_for from
user_tab_subpartitions;
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBPARTITION_NAME
COMPRESS_FOR
--------------------------------------------------------------------------------------------------------------------------------
------------------------------
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116124
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116125
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116126
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116127
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116128
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116129
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116130
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116131
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116132
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116133
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116134
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116135
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116136
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116137
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116138
ARCHIVE HIGH
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116139
ARCHIVE HIGH
16 rows selected.
Regards
Maxim
On Fri, May 21, 2021 at 10:40 AM Fairlie Rego <fairlie.rego@xxxxxxxxx>
wrote:
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
