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.
Pingback: Reorganize a table in Oracle database with CTAS method | Smart way of Technology