Automatic coalesce of 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 8.1.6.1.0 for Sun Solaris and on 8.1.6.0.0 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:

coale.sh <sid> <user> <passwd>

#
# Arguments
sid=$1
user=$2
passwd=$3
#
# 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';
exit
SQLEND
}

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

alter tablespace $1 coalesce;

exit
SQLEND
}
#

tslista=$(nomits)

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

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 8.1.6.0.0 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.