Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

changing default tablespace for lob within partitioned table

user11980810Aug 23 2011 — edited Jul 14 2013
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 11 2013
Added on Aug 23 2011
4 comments
4,633 views