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 

For More Information

  • What do you think about this tip? E-mail us at

    Requires Free Membership to View

This was first published in October 2001

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.