Defrag and Oracle TableSpace

1. Create new fresh tablespace

CREATE TABLESPACE
DATAFILE...;

2. Move objects

BEGIN
FOR i IN (SELECT 'ALTER TABLE '||owner||'.'||tables_name||' MOVE TABLESPACE '||<tablespace cible> STMT FROM dba_tables WHERE tablespace_name = <tbs à défragmenter>) LOOP
execute immediate(i.STMT);
 END LOOP;
FOR i IN (SELECT 'ALTER INDEX '||owner||'.'||tables_name||' REBUILD  TABLESPACE '||<tablespace cible> STMT FROM dba_indexes WHERE tablespace_name = <tbs to defrag>) LOOP execute immediate(i.STMT);
END LOOP;
END;
/
</tbs></tablespace></tbs></tablespace>

3. Delete old tablespace
Be sure its no more used and empty.

DROP TABLEPACE ;

4. Delete Physical datafiles

5. Recreate Tablespace

6. Do it again , back to the original (new) tablespace

7. Recompile Objects

sqlplus system/manager@mabase
@$ORACLE_HOME/rdbms/utlrp.sql

Be the first to comment

Leave a Reply

Your email address will not be published.


*


*