Defragmenting tablespaces

DBAs often run into problems defragmenting tablespaces, especially the temporary tablespace. Here is way to do...

it efficiently:

alter tablespace 
 
   default storage (pctincrease 1) ;             
alter tablespace TEMPORARY coalesce ;                              
alter tablespace 
  
    default storage (pctincrease 0) ;             

  
 

You should now experience a dramatic improvement. You can do the same for other tablespaces as well. If you want to do the same for all tablespaces, here is a script which will create a dynamic script to create the sql scripts and then run it:

connect system/manager@dbname 

set head off 
set feed off 

spo do_coal 

select 'alter tablespace '|| tablespace_name || ' default storage (pctincrease 1) ;' 
  from dba_tablespaces 
/ 

select 'alter tablespace '|| tablespace_name || ' coalesce ;' 
  from dba_tablespaces 
/ 

select 'alter tablespace '|| tablespace_name || ' default storage (pctincrease 0) ;' 
  from dba_tablespaces 
/ 

set head on 
set feed on 

spo off 
@do_coal.lst 

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.