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

Automatic coalesce of all tablespaces

This Korn shell script will coalesce all tablespaces.

Coalescing tablespace extents is fundamental when the database contains a lot of fragmentated tablespaces. This happens when database objets are often truncated or droppend and re-created. This simple korn shell script helps to automate cleaning of tablespaces. Using this script, the DBA can schedule a regular tablespace coalesce (always advisable) without any manual intervention. It has been tested on Oracle version for Sun Solaris and on on Windows 2000. The code uses simple SQL commands, so I'm sure it can be used on any Oracle version.

I use the script in Windows using the Korn shell emulation from Microsoft Windows Service for Unix. In the Windows Korn shell just set an alias "sqlplus" for SQLPLUS.EXE.

To use the script type: <sid> <user> <passwd>
You need a DBA account and the database must be local.
# Arguments
# Variables
export ORACLE_SID=$sid
# Functions
function nomits
sqlplus -S $user/$passwd << SQLEND
set pagesize 0
set heading off
set feedback off
select tablespace_name from dba_tablespaces
where contents='PERMANENT' and status='ONLINE';

function coale
sqlplus -S $user/$passwd << SQLEND

alter tablespace $1 coalesce;



for ts in $tslista
    out=$(coale $ts | grep '..*' )
 if [ "$out" = "Tablespace altered." ]
               echo " Tablespace $ts: coalesce completed."
               echo " Tablespace $ts: error while coalescing,"
               echo " See alert file for more information."

exit 0

Reader Feedback

Brian Peasland writes: The author of the tip uses the ALTER TABLESPACE COALESCE command. If the author simply sets the tablespace's default PCTFREE to a non-zero value (most DBAs have used '1' in the past), then SMON will automatically coalesce free space for you. There is no need to manually perform this operation.

But the whole point becomes moot beginning with Oracle 8i. Oracle 8i introduced the Locally Managed Tablespace (LMT). With LMT, one does not have to coalesce any free space! Most Oracle DBAs are either using LMTs, or migrating to them. This is a much more efficient method.

The author responds: Of course Brian Peasland is right (but the parameter should be PCTINCREASE not PCTFREE). I meant to elaborate that this script is useful in two conditions: 1) if you have several big dictionary managed tablespaces (that is, there is no time to migrate to locally managed tablespaces) and 2) if you fear that SMON isn't working very well (SMON is sadly famous for being buggy in Oracle on Sun Solaris).

I prefer using PCTINCREASE 0, uniform allocation (mostly for the newly created object) even if a 1% increase really isn't a lot and lets the SMON sleep. Running the script costs almost zero, and you can schedule it when there is less activity on the DB. However, I do agree that locally managed tablespaces are the best solution.

Brian Peasland responds: The author does have a point. But it becomes one of those things where you can just argue "best" over "better" and the points start to get nitpicky. The author states that there have been problems with SMON on Oracle 8.1.6, which is very true. But I'd then say to upgrade to a higher, supported version. So it just goes back and forth. Both opinions are correct, so I'd just leave it there.

David G. writes: I agree with Brian Peasland's second comment. Locally Managed Tablespaces are the way to go, not this script. I realize this script may not cause a lot of overhead but by the time you set it up and test it you could have converted any dictionary managed tablespaces (in 8i and above) to LMT's. You no longer need to worry about fragmented tablespaces but will also realize improved performance overall.

Brad W. writes: Locally Managed tablespaces are the way to go. They are rumored to be the only option available in 10i which is due out this year sometime...

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture