Moving objects from one tablespace to another

set serveroutput on

set lines 300

DECLARE           

              V_CURRENT_TABLESPACE VARCHAR2(50);

              V_DEST_TABLESPACE VARCHAR2(50);

              V_SQL VARCHAR2(1000);

              V_OVERFLOW NUMBER := 0;

BEGIN

              V_CURRENT_TABLESPACE := '&current_tablespace';

              V_DEST_TABLESPACE := '&destination_tablespace';

              FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE)

              LOOP

             

                             DBMS_OUTPUT.PUT_LINE('MOVING TABLES:');

                             DBMS_OUTPUT.PUT_LINE('------------------');

                             FOR LIST_TABLES IN (SELECT TABLE_NAME, IOT_NAME FROM DBA_TABLES WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE)

                            

LOOP

                                           IF  LIST_TABLES.IOT_NAME IS NOT NULL THEN

                                                          DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE ' || LIST_TABLES.IOT_NAME || '...');

                                                          V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE TABLESPACE ' || V_DEST_TABLESPACE;

n  -- Alter Table IOT_Table_Name Move Tablespace New_Tablespace_Name

                                                         

                                                          EXECUTE IMMEDIATE V_SQL;

                                                          -- check if an overflow exists

                                                          DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE OVERFLOW ' || LIST_TABLES.IOT_NAME || '...');

                                                          V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE OVERFLOW TABLESPACE ' || V_DEST_TABLESPACE;                                                   

                                                          EXECUTE IMMEDIATE V_SQL;

                                           ELSE                    

                                                          DBMS_OUTPUT.PUT_LINE('MOVING TABLE ' || LIST_TABLES.TABLE_NAME || '...');

                                                          V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.TABLE_NAME || '" MOVE TABLESPACE ' || V_DEST_TABLESPACE;                                           

                                                          EXECUTE IMMEDIATE V_SQL;

                                           END IF;

                             END LOOP;

                            

                             DBMS_OUTPUT.PUT_LINE(' ');

                             DBMS_OUTPUT.PUT_LINE('MOVING LOBS:');

                             DBMS_OUTPUT.PUT_LINE('------------------');

                            

                             FOR LIST_LOBS IN (SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE)

                             LOOP

                                           DBMS_OUTPUT.PUT_LINE('MOVING LOB FROM TABLE ' || LIST_LOBS.TABLE_NAME || ' TO TABLESPACE ' || V_DEST_TABLESPACE || '...');

                                           V_SQL := 'ALTER TABLE ' || LIST_OWNER.OWNER || '.' ||  LIST_LOBS.TABLE_NAME || ' MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_DEST_TABLESPACE || ')';

                                           EXECUTE IMMEDIATE V_SQL;

                             END LOOP;                      

                            

                             DBMS_OUTPUT.PUT_LINE(' ');

                             DBMS_OUTPUT.PUT_LINE('MOVING INDEXES:');

                             DBMS_OUTPUT.PUT_LINE('------------------');

                            

                             FOR LIST_INDEXES IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE AND SEGMENT_TYPE='INDEX')

                             LOOP

                                           DBMS_OUTPUT.PUT_LINE('MOVING ' || LIST_INDEXES.SEGMENT_TYPE || ' ' || LIST_INDEXES.SEGMENT_NAME || '...');                                          

                                           V_SQL := 'ALTER INDEX ' || LIST_OWNER.OWNER  || '."' || LIST_INDEXES.SEGMENT_NAME || '" REBUILD TABLESPACE ' || V_DEST_TABLESPACE;

                                           EXECUTE IMMEDIATE V_SQL;

n  ALTER INDEX Inex_Name REBUILD TABLESPACE New_Tablespace_Name;

                             END LOOP;                                     

                             DBMS_OUTPUT.PUT_LINE(' ');                 

                            

              END LOOP;

END;