changing default tablespace for lob within partitioned table
Hi,
this is more a sql question asking for the correct syntax:
Within 11g we have a range partitioned table containing a lob column. After some time the requirements change and we decided to move the lob segment into another Tablespace. We also want to modify the default tablespace for the lob segment (and only for the lob segment), as we don't want to specify the tablespace for the lob segment each time when creating new partitions. My question is what is the correct syntax for this task.
Any help is appreciated.
CREATE TABLE "PART_TEST"
(
"ID" NUMBER(*,0),
"BLOB_DATA" BLOB
) TABLESPACE "USERS_DS6F"
LOB ("BLOB_DATA") STORE AS SECUREFILE
PARTITION BY RANGE ("ID")
(
PARTITION "P1" VALUES LESS THAN (100)
) ;
select lob_name, def_tablespace_name from user_part_lobs where table_name='PART_TEST';
-> def_tablespace_name is null
alter table part_test move partition p1 lob (blob_data) store as ( tablespace QC_BULK_DATA_1 ) ;
select lob_name, partition_name, tablespace_name from user_lob_partitions where table_name='PART_TEST';
-> lob segment of P1 is in QC_BULK_DATA_1
alter table part_test add partition "P2" values less than (200);
select lob_name, partition_name, tablespace_name from user_lob_partitions where table_name='PART_TEST';
-> lob segment of P1 is in "QC_BULK_DATA_1" whereas P2 is in "USERS_DS6F"
alter table part_test modify lob(blob_data) store as (tablespace qc_bulk_data_1) *
ERROR at line 1:
ORA-00906: missing left parenthesis
Thanks a lot, Hannes