Reorganize the table with move to another tablespace

1. Create a new tablespace if you have for table movement then its good otherwise create one.

create tablespace data_temp datafile 'E:\oracle\oradata\data_temp.dbf'
size 10M autoextend on next 10M maxsize 200M;


2. Check the invalid objects

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;


3. Check the table you want to move to new tablespace.
Following command will help the table to move to new tablespace.

--Check the default tablespace for table.
select tablespace_name from dba_tables where table_name='TEST1';

-- Move to DATA_TEMP tablespace
Alter table SCOTT.EMPLOYEE move tablespace DATA_TEMP;


4. If you have index associated with table and you also want to move it in new tablespace.

Alter INDEX SCOTT.TEST1_IDX REBUILD tablespace DATA_TEMP;


5. If any lob object is assicasted with table.

-- SYNTAX
alter table OWNER.TABLE_NAME move lob (COLUMN_NAME) store as LOB_NAME ( tablespace TABLESPACE_NAME);

--EXAMPLE
alter table SCOTT.EMPLOYEE move lob (resume) store as SYS_LOB0000018376C00 (tablespace DATA_TEMP);


6. After movement all objects associated to table. check invalid objects

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;


7. Check unstable index if found then make them stable.

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';


8. Check new tablespace of table

select tablespace_name from dba_tables where table_name='TEST1';


9. Assign permission to user for new tablespace if needed.