Space Reclamation Procedure for Oracle tables - Move tablespace

 Space Reclamation Procedure for oracle objects: move tablespace

There are various space reclamation procedures available for the oracle database and the tables. Below is one of the methodologies to move all the tables and objects to the new tablespace. Though I have run it successfully in an environment, I remember correcting it while running it. 

Pre-requisites:

1. Run it in test environment before running it in any important environment.

2. Have enough space in the new tablespace before initiating the scripts. 

3. If a lob segment is not moved, then that particular table's data has to be moved to a new table created. 

4. Always connect to the schema to be moved to run the scripts. Must not run as a 'SYS' user. 

5. Tablespaces 'TBSDATA' and 'TBSINDEX' were created for the below example. 

TEMP TABLES:

CREATE TABLE RECLAIM_SEGMENTS AS SELECT * FROM USER_SEGMENTS; CREATE TABLE COMPLETED_TABLES AS SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE 1=2; CREATE TABLE NOT_COMPLETED AS SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE 1=2;

PL/SQL PROCEDURE: 

SET SERVEROUTPUT ON BEGIN FOR cur_rec IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM  RECLAIM_SEGMENTS ) LOOP BEGIN EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' enable row movement '; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' MOVE TABLESPACE TBSLIVEDATA  parallel 20 nologging '; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' MOVE LOB(XMLRECORD) STORE AS (TABLESPACE TBSDATA)  parallel 20 nologging '; BEGIN FOR cur_index in (select INDEX_NAME from DBA_INDEXES where table_name='' || cur_rec.SEGMENT_NAME  || ''   and INDEX_NAME NOT LIKE '%SYS%'  and INDEX_NAME NOT LIKE '%LOB%'    and owner='USER') LOOP EXECUTE IMMEDIATE 'ALTER INDEX  ' || cur_index.INDEX_NAME ||' REBUILD TABLESPACE TBSINDEX  parallel 10 nologging '; END LOOP; END; EXECUTE IMMEDIATE 'INSERT INTO COMPLETED_TABLES  VALUES (''' || cur_rec.SEGMENT_NAME || ''')'; EXECUTE IMMEDIATE 'DELETE FROM RECLAIM_SEGMENTS WHERE SEGMENT_NAME ='''|| cur_rec.SEGMENT_NAME || ''''; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' disable row movement '; Commit; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'INSERT INTO NOT_COMPLETED  VALUES (''' || cur_rec.SEGMENT_NAME || ''')'; DBMS_OUTPUT.put_line('FAILED: COULD NOT BE ALTERED ' || cur_rec.SEGMENT_TYPE || ' "' || cur_rec.SEGMENT_NAME || '"'); END; END LOOP; END; /