Monday, August 7, 2017

Drop datafiles from a tablespace

This is an important question that I have heard a lot these days. The question is how we can remove a data file from a tablespace in oracle and what is the solution for doing this issue  and It is possible or not.I should say please concentrate on this subject that when you add a datafile in a specific tablespace Oracle distribute the extends on all datafiles for improving I/O, therefore be careful If you remove a datafile you will lost your data.What is the solution If we want to drop some datafiles from a tablespace?
Create a new tablespace in your database and move all of your segments on it for example Table, Index, MVW, Partititon and Subpartitions.

--Move Table
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;

--Move Index
select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

--Move Partition Index
ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace> NOLOGGING;

--Move Partition
alter table owner.your_table move partition p1 tablespace TBS4 update global indexes;
in 12c:
alter table owner.your_table move partition p1 tablespace TBS4 online;

--Move LOBs:
SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';

ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);


After that you move all your segments you can drop old tablespace and rename new tablespace with old tablespace name.

No comments:

Post a Comment