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 
@do_coal.lst 

For More Information


This was first published in October 2001

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close