Problem solve Get help with specific problems with your technologies, process and projects.

Defragmenting tablespaces

Two scripts that will defragment temporary and all 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

This was last published in October 2001

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.