DBAs often run into problems defragmenting tablespaces, especially the temporary tablespace. Here is way to do...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.