Oracle Tablespace Coalesce Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse

Manual Tablespace Coalescing

If you detect that a single tablespace has fragmented, you can quickly coalesce it with the following procedures:

1. Alter session by retrieving the tablespace number from sys.ts$:

select * from sys.ts$;

2. In SQL*Remote DBA, issue the following command:

alter session set events ‘immediate trace name coalesce level &tsnum);
(where tsnum is the tablespace number from step 1)

3. Manual coalesce; from SQL*Plus enter:

alter tablespace <xxxx> coalesce;

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.