DBAs often run into problems defragmenting tablespaces, especially the temporary tablespace. Here is way to do it efficiently:
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.
This was first published in October 2001