Re: Compress lob subpartition

From: Tim Gorman <tim.evdbt_at_gmail.com>
Date: Fri, 21 May 2021 12:29:01 -0700
Message-ID: <4b390564-07d7-d382-7d89-fb4090395e95_at_gmail.com>

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_at_gmail.com
> <mailto: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.technoconsulting.com.au>
> http://www.linkedin.com/in/fairlierego
> <http://www.linkedin.com/in/fairlierego>
> https://fairlierego.wordpress.com/
> <https://fairlierego.wordpress.com/>
>
> Twitter _at_fairlierego
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 21 2021 - 21:29:01 CEST

This message: [ Message body ]
Next message: Pap: "Re: PGA on higher version"
Previous message: Jeff Smith: "RE: [External] : RE: OT- raw meat in these days of devops"
In reply to Maxim: "Re: Compress lob subpartition"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message