Move all objects tablespace

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

BEGIN
FOR rec_users IN (
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME
FROM ALL_TABLES UT
WHERE TEMPORARY = 'N'
AND NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM')
)
LOOP
EXECUTE IMMEDIATE 'ALTER USER '||rec_users.OWNER||' QUOTA UNLIMITED ON DATA_TBS_DATA';
EXECUTE IMMEDIATE 'ALTER USER '||rec_users.OWNER||' QUOTA UNLIMITED ON DATA_TBS_INDEX';
END LOOP;

FOR rec_tables IN (
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME
FROM ALL_TABLES
WHERE TEMPORARY = 'N'
AND NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_tables.OWNER||'.'||rec_tables.TABLE_NAME||' MOVE TABLESPACE DATA_TBS_DATA';
DBMS_OUTPUT.PUT_LINE('Table '||rec_tables.OWNER||'.'||rec_tables.TABLE_NAME||' tablespace moved successfully!!!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for table '||rec_tables.OWNER||'.'||rec_tables.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_part_tables IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME 
FROM ALL_TAB_PARTITIONS
WHERE SUBPARTITION_COUNT=0
AND NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_part_tables.TABLE_OWNER||'.'||rec_part_tables.TABLE_NAME||' MOVE PARTITION '||rec_part_tables.PARTITION_NAME||' TABLESPACE DATA_TBS_DATA';
DBMS_OUTPUT.PUT_LINE('Successfully moved tablespace for partition table '||rec_part_tables.TABLE_OWNER||'.'||rec_part_tables.TABLE_NAME||' on partition '||rec_part_tables.PARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for partition table '||rec_part_tables.TABLE_OWNER||'.'||rec_part_tables.TABLE_NAME||' on partition '||rec_part_tables.PARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_subpart_tables IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME,SUBPARTITION_NAME,TABLESPACE_NAME 
FROM ALL_TAB_SUBPARTITIONS
WHERE NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_subpart_tables.TABLE_OWNER||'.'||rec_subpart_tables.TABLE_NAME||' MOVE SUBPARTITION '||rec_subpart_tables.SUBPARTITION_NAME||' TABLESPACE DATA_TBS_DATA';
DBMS_OUTPUT.PUT_LINE('Successfully moved tablespace for sub partition table '||rec_subpart_tables.TABLE_OWNER||'.'||rec_subpart_tables.TABLE_NAME||' on partition '||rec_subpart_tables.SUBPARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for subpartition table '||rec_subpart_tables.TABLE_OWNER||'.'||rec_subpart_tables.TABLE_NAME||' on partition '||rec_subpart_tables.SUBPARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;  

FOR rec_indexes IN (
SELECT OWNER,INDEX_NAME,TABLESPACE_NAME
FROM ALL_INDEXES
WHERE INDEX_TYPE <> 'LOB'
AND STATUS <> 'N/A'
AND NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','TEMP')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_indexes.OWNER||'.'||rec_indexes.INDEX_NAME||' REBUILD TABLESPACE DATA_TBS_INDEX';
DBMS_OUTPUT.PUT_LINE('Index tablespace for '||rec_indexes.OWNER||'.'||rec_indexes.INDEX_NAME||' moved successfully!!!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for index '||rec_indexes.OWNER||'.'||rec_indexes.INDEX_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_part_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME 
FROM ALL_IND_PARTITIONS
WHERE SUBPARTITION_COUNT=0
AND NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','TEMP')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_part_indexes.INDEX_OWNER||'.'||rec_part_indexes.INDEX_NAME||' REBUILD PARTITION '||rec_part_indexes.PARTITION_NAME||' TABLESPACE DATA_TBS_INDEX STORAGE(INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Successfully moved tablespace for index '||rec_part_indexes.INDEX_OWNER||'.'||rec_part_indexes.INDEX_NAME||' on partition '||rec_part_indexes.PARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for index '||rec_part_indexes.INDEX_OWNER||'.'||rec_part_indexes.INDEX_NAME||' on partition '||rec_part_indexes.PARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_subpart_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,SUBPARTITION_NAME,TABLESPACE_NAME
FROM ALL_IND_SUBPARTITIONS
WHERE NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','TEMP')
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_subpart_indexes.INDEX_OWNER||'.'||rec_subpart_indexes.INDEX_NAME||' REBUILD SUBPARTITION '||rec_subpart_indexes.SUBPARTITION_NAME||' TABLESPACE DATA_TBS_INDEX';
DBMS_OUTPUT.PUT_LINE('Successfully moved tablespace for index '||rec_subpart_indexes.INDEX_OWNER||'.'||rec_subpart_indexes.INDEX_NAME||' on supartition '||rec_subpart_indexes.SUBPARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move tablespace for index '||rec_subpart_indexes.INDEX_OWNER||'.'||rec_subpart_indexes.INDEX_NAME||' on subpartition '||rec_subpart_indexes.SUBPARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_lobs IN (
SELECT DISTINCT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME
FROM ALL_LOBS
WHERE NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','TEMP')
AND (OWNER,TABLE_NAME) NOT IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME
FROM ALL_LOB_PARTITIONS
)
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||rec_lobs.OWNER||'.'||rec_lobs.TABLE_NAME||' MOVE LOB ('||rec_lobs.COLUMN_NAME||') STORE AS '||rec_lobs.SEGMENT_NAME||' (TABLESPACE DATA_TBS_DATA)';
DBMS_OUTPUT.PUT_LINE('Successfully moved LOB '||rec_lobs.OWNER||'.'||rec_lobs.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to move LOB '||rec_lobs.OWNER||'.'||rec_lobs.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_lob_parts IN (
SELECT DISTINCT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,COLUMN_NAME,TABLESPACE_NAME
FROM ALL_LOB_PARTITIONS
WHERE NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','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_DATA)';
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,TABLESPACE_NAME 
FROM ALL_LOB_SUBPARTITIONS
WHERE NVL(TABLESPACE_NAME,'NULL') NOT IN ('SYSAUX','SYSTEM','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_DATA)';
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