Re: Compress lob subpartition
Date: Fri, 21 May 2021 11:55:43 +0200
Message-ID: <CAFP4yMypd5S0F3GQb2RFS07iJ3B8r=d6q-LhZ4gjwg5gv3poRQ_at_mail.gmail.com>
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>
TABLE_NAME
PARTITION_NAME
SUBPARTITION_NAME
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116125
SQL> select table_name,partition_name, subpartition_name, compress_for from
user_tab_subpartitions;
COMPRESS_FOR
--------------------------------------------------------------------------------------------------------------------------------
------------------------------
SHOW_LOB_STORAGE
PAYM_INTCHG_INIT_PART
SYS_SUBP2116124
ARCHIVE HIGH
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_at_gmail.com> 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 _at_fairlierego
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 21 2021 - 11:55:43 CEST