2

Consider following tables:

CREATE TABLE TAB_ONE
(
  <irrelevant columns>
  MESSAGE CLOB,
  REC_DATE DATE,
  <irrelevant columns>
) TABLESPACE DATA_TS;

and

CREATE TABLE TAB_TWO
(
  <irrelevant columns>
  RESPONSE CLOB,
  PART_ID NUMBER,
  <irrelevant columns>
  CONSTRAINT "FK_01"
    FOREIGN KEY ("PART_ID") REFERENCES  <IRRELEVANT_TABLE> ("SEQ_ID")
) TABLESPACE DATA_TS PARTITION BY REFERENCE (FK_01) ENABLE ROW MOVEMENT;

now, the task is to move all the (C)LOBs from DATA_TS to newly allocated tablespace called LOB_TS.

For the first table, it is easy enough: 

ALTER TABLE TAB_ONE MOVE LOB ("MESSAGE") store as (tablespace LOB_TS compress low);

For the other one, partitioning does all the trouble. The aforementioned command does not work for obvious reasons, so I managed to find another:

ALTER TABLE TAB_TWO MOVE PARTITION SYS_P18485 LOB (RESPONSE) STORE AS ( TABLESPACE LOB_TS COMPRESS LOW );
ALTER TABLE TAB_TWO MOVE PARTITION SYS_P18299 LOB (RESPONSE) STORE AS ( TABLESPACE LOB_TS COMPRESS LOW );

(one for each partition the table TAB_TWO has)

These ALTER TABLES do not fail per se. The SQL Developer proudly states "Table TAB_TWO altered."

But then I ran SELECT * FROM USER_LOBS WHERE TABLE_NAME = 'TAB_TWO' and found out, that the CLOB stayed in the previous tablespace and did not move.

Of course, the idea of copying data via Create Table as Select, dropping the table, creating a new table and restoring the data occurred to me, but I would prefer a cleaner solution without the need of duplicating large amounts of data to different tables.

erewien
  • 349
  • 4
  • 15
  • what if you query: SELECT * FROM USER_LOB_PARTITIONS WHERE TABLE_NAME = 'TAB_TWO'; – Cyrille MODIANO Oct 24 '17 at 14:35
  • Yes, those are correct. Although would not the different tablespaces on table and its partitions cause trouble in the long run? (probably stupid question but I am not really fluent in databases) – erewien Oct 25 '17 at 06:48
  • what's the output of: select count(*), segment_type, tablespace_name from user_segments group by segment_type, tablespace_name; – Cyrille MODIANO Oct 25 '17 at 08:36
  • Sorry for abandoning the thread, I have been busy with the priority fixes lately. I have gone through the queries, outside of mundate things I think the interesting things are 47 LOBSEGMENT DATA_TS alongside 5 LOB PARTITION DATA_TS and 29 LOB PARTITION LOB_TS It just seems I forgot about some tables when doing the script – erewien Nov 01 '17 at 11:30

0 Answers0