Reorganize the table with move to another tablespace

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.

1 thought on “Reorganize the table with move to another tablespace

  1. Pingback: Reorganize a table in Oracle database with CTAS method | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.