set echo off
set feedback off
set heading off
select 'alter tablespace '||tablespace_name||' coalesce ;' from dba_tablespaces;
set feedback on
set echo on
03-01-2001, 11:01 AM
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.
03-01-2001, 11:07 AM
That 1% PCTINCREASE leads to fragmentatiom as you know!
03-01-2001, 11:11 AM
What is locally managed tablespaces and how do you test it?
03-01-2001, 02:59 PM
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.