Please note that the command specifies COMPRESS HIGH ONLINE instead of
COMPRESS ARCHIVE HIGH ONLINE or COMPRESS QUERY HIGH ONLINE.
Here is the output you posted...
SQL> 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
Is that a syntax error, or just a copy/paste error within this email?
On 5/21/2021 2:55 AM, Maxim wrote:
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 <mailto: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.technoconsulting.com.au>
http://www.linkedin.com/in/fairlierego
<http://www.linkedin.com/in/fairlierego>
https://fairlierego.wordpress.com/
<https://fairlierego.wordpress.com/>
Twitter @fairlierego
