Move table and dependent LOB to other tablespace

Requirement :-

User: RCAADMIN9

• Create  tablespace DATA_SWAP_RCA9 with datafile DATA_SWAP_RCA9_01.dbf  
• Create  tablespace LOB_SWAP_RCA9 with datafile LOB_SWAP_RCA9_01.dbf  
• Move SWAP_RCA9 table from tablespace DATA_DEMO_RCA9 to tablespace DATA_SWAP_RCA9   
• and LOB columns to LOB_SWAP_RCA9 

Here are the steps for implementation and validation:-

col FILE_NAME for a70

set long 999999

col COLUMN_NAME for a20

col TABLE_NAME for a20

set lines 300

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name='DATA_SWAP_RCA9';

no rows selected

create tablespace DATA_SWAP_RCA9 datafile '/database/oradata6/DATA_SWAP_RCA9_01.dbf' size 1G autoextend on ;

create tablespace LOB_SWAP_RCA9 datafile '/database/oradata6/LOB_SWAP_RCA9_01.dbf' size 10G autoextend on ;

check the ddl of SWAP_RCA9

SELECT DBMS_METADATA.GET_DDL('TABLE','SWAP_RCA9','RCAADMIN9') FROM dual ;

Move tables & LOB :-

alter table  RCAADMIN9.SWAP_RCA9 move tablespace DATA_SWAP_RCA9;

ALTER TABLE  RCAADMIN9.SWAP_RCA9 MOVE  LOB ("B_DATA") STORE AS (TABLESPACE LOB_SWAP_RCA9);

Validation :-

select tablespace_name, file_name ,bytes/1024/1024 "in MB" from dba_data_files where tablespace_name='DATA_SWAP_RCA9';

TABLESPACE_NAME                FILE_NAME                                                                   in MB

------------------------------ ---------------------------------------------------------------------- ----------

DATA_SWAP_RCA9                 /database/oradata6/DATA_SWAP_RCA9_01.dbf                               1024

select tablespace_name, file_name ,bytes/1024/1024 "in MB" from dba_data_files where tablespace_name='LOB_SWAP_RCA9';

select segment_name,tablespace_name, segment_name ,segment_type, bytes/1024/1024 "in MB" from dba_segments

where segment_name  ='SWAP_RCA9';

SEGMENT_NAME         TABLESPACE_NAME                SEGMENT_NAME         SEGMENT_TYPE            in MB

-------------------- ------------------------------ -------------------- ------------------ ----------

SWAP_RCA9            DATA_SWAP_RCA9                 SWAP_RCA9            TABLE                   .0625

select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs

where table_name='SWAP_RCA9'

;

OWNER                          TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME

------------------------------ -------------------- -------------------- ------------------------------ ------------------------------

RCAADMIN9                      SWAP_RCA9            B_DATA               SYS_LOB0000102097C00003$$      LOB_SWAP_RCA9

SELECT DBMS_METADATA.GET_DDL('TABLE','SWAP_RCA9','RCAADMIN9') FROM dual ;