Re-Organize / Defrag database schema using ALTER TABLE MOVE TABLESPACE option

There are several ways for a complete reorganization and space reclamation:-
  • EXPORT/IMPORT
  • DBMS_REDEFINITION
  • CREATE TABLE AS SELECT (CTAS)
  • DATA PUMP
  • ALTER TABLE MOVE TABLESPACE
This post provides a trick to reorganize all schema objects using the following script using ‘ALTER TABLE MOVE TABLESPACE’ option.
 
— with LOB and partition segments
— Move tables
select ‘ALTER TABLE ‘ || OWNER || ‘.’ || TABLE_NAME || ‘ MOVE TABLESPACE ‘ || ‘DASCORE_37’ || ‘;’ from DBA_TABLES WHERE OWNER IN (‘DASCORE_37’);
— Rebuild indexes
select ‘ALTER INDEX ‘ || OWNER || ‘.’ || INDEX_NAME || ‘ REBUILD TABLESPACE ‘ || ‘DASCORE_37’ || ‘;’ from DBA_INDEXES WHERE OWNER IN (‘DASCORE_37’);
— Move a table partition segment. 
select ‘ALTER TABLE ‘ || TABLE_OWNER || ‘.’ || TABLE_NAME || ‘ MOVE PARTITION ‘ || partition_name || ‘ TABLESPACE ‘ || ‘DASCORE_37’ || ‘ NOLOGGING;’ from DBA_TAB_PARTITIONS WHERE TABLE_OWNER IN (‘DASCORE_37’);
–ALTER TABLE tab1 MOVE PARTITION part_1 TABLESPACE new_ts NOLOGGING;
 
— Rebuild an index partition segment.
select ‘ALTER INDEX ‘ || INDEX_OWNER || ‘.’ || INDEX_NAME || ‘ REBUILD PARTITION ‘ || partition_name || ‘ TABLESPACE ‘ || ‘DASCORE_37’ || ‘ NOLOGGING;’ from DBA_IND_PARTITIONS WHERE INDEX_OWNER IN (‘DASCORE_37’);
–ALTER INDEX ind1 REBUILD PARTITION ind1_part1 TABLESPACE new_ts;
 
— Move LOB segments if we had them.
select ‘ALTER TABLE DASCORE_37.’|| table_name || ‘ MOVE LOB(‘|| column_name || ‘) STORE AS (TABLESPACE DASCORE_37);’ from dba_tab_columns 
where owner like ‘DASCORE_37’ and data_type like ‘%LOB%’;
–ALTER TABLE tab1 MOVE LOB(lob_column_name) STORE AS (TABLESPACE new_ts);
One need to run the execute the output of dynamic SQLs run above to reorganize and de-fragment tablespace and free up disk space.
Advertisements
This entry was posted in Database Technologies, Oracle and tagged , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s