Log in
updated 11:29 AM UTC, May 4, 2016

Move table and dependent LOB to other tablespace

  • Written by Manjunatha Srinivas
  • Published in Database

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 ;


Powered by Bullraider.com
Follow Us on Twitter
Find Us on Facebook
Follow Us on Google
Follow Us on Pinterest