MOVE LOB Partitions

SET SERVEROUTPUT ON
SET FEEDBACK ON VERIFY ON HEADING ON ECHO ON TIMING ON
SPOOL MoveLobPartsTBS.log

BEGIN
FOR rec_lob_parts IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,COLUMN_NAME 
FROM ALL_LOB_PARTITIONS
WHERE TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM','DATA_TBS','TEMP')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_lob_parts.TABLE_OWNER||'.'||rec_lob_parts.TABLE_NAME||' MOVE PARTITION '||rec_lob_parts.PARTITION_NAME||' LOB ('||rec_lob_parts.COLUMN_NAME||') STORE AS (TABLESPACE DATA_TBS)';
DBMS_OUTPUT.PUT_LINE('Successfully moved LOB '||rec_lob_parts.TABLE_OWNER||'.'||rec_lob_parts.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move LOB partition '||rec_lob_parts.PARTITION_NAME||' of '||rec_lob_parts.TABLE_OWNER||'.'||rec_lob_parts.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_lob_subparts IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME,SUBPARTITION_NAME,COLUMN_NAME 
FROM ALL_LOB_SUBPARTITIONS
WHERE TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM','DATA_TBS','TEMP')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_lob_subparts.TABLE_OWNER||'.'||rec_lob_subparts.TABLE_NAME||' MOVE SUBPARTITION '||rec_lob_subparts.SUBPARTITION_NAME||' LOB ('||rec_lob_subparts.COLUMN_NAME||') STORE AS (TABLESPACE DATA_TBS)';
DBMS_OUTPUT.PUT_LINE('Successfully moved LOB '||rec_lob_subparts.TABLE_OWNER||'.'||rec_lob_subparts.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move LOB partition '||rec_lob_subparts.SUBPARTITION_NAME||' of '||rec_lob_subparts.TABLE_OWNER||'.'||rec_lob_subparts.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

END;
/

SPOOL OFF