If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

There is a limitation in Oracle that if u do coalesce once, it may not squeeze through whole of the tablespace, u have to more than once for it to squeeze.
If tablespace is large, it might take long time and high CPU usage to coalesce.
Now which tablespace u want to coalesce,table or index.
Table: As said before in thread, design tablespace and table locations accordingly and put uniform extent size for all tables on a particular tablespace.

Or put pctincrease 1% at tablespace level for smon to work and during table creation put pctincrease as 0% or as reqd.

Test locally managed tablespace before use, there are few bugs in 8.1.6.

A locally managed tablespace uses bitmaps to track extent information, instead of dictionary tables (available only on Oracle 8i). Since Oracle itself manages the extents, you don't have to worry about coalescing. LMT's also reduces the access to dictionary tables, improving performance of the database. It could be a good idea to adopt this feature, but I don't know if it is possible to change your Dictionary-Managed to a Locally-Managed-Tablespace. Probably you'd have to re-create it. Could anyone confirm that?

Maybe you should also consider the use of uniform extents and/or PCTINCREASE 0, to reduce fragmentation and the need of coalescing.