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 last published in October 2001

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close