colaescing free space at reglar intervals

[hrishy]

could anybody gime a shell script that i can run through crontab or at command of nt so that it can colasce free space in tablespaces and colasce it say every friday.

can anbody also give me a an example how this can be done through DBMS_JOB utility.

i am runing 8i on linux and NT


thanks in advance

[jmodic]

Why don't you just switch to localy managed tablespaces and forget about coalescing.....

[hrishy]

at present there are some databses running on oarcle 8 to i forgot to mention that sorry folks

[pando]

or use uniform extent size and forget about coalescing

[sreddy]

There you go: Jmodic's advise, the best to reduce the pain on your head from day to day tasks.

[sreddy]

Hey, hrishy!

Itsn't that difficult write one on your own. All you need to do is:
Set up oracle_home and oracle_sid in shell script and call the sql script as follows.

Unix script
<font face=courier>
setenv ORAENV_ASK NO
setenv ORACLE_SID xyz
source /usr/bin/coraenv
sqlplus -s abc/**** @coal_tbs.sql
</font>
SQL script(coal_tbs.sql)
<font face=courier>
set echo off
set feedback off
set heading off
spool coaltbs.sql

select 'alter tablespace '||tablespace_name||' coalesce ;' from dba_tablespaces;

spool off
set feedback on
set echo on
</font>

[gpsingh]

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.

[sreddy]

That 1% PCTINCREASE leads to fragmentatiom as you know!
Just reminder.

[badrinathn]

What is locally managed tablespaces and how do you test it?
Badrianth

[adrianomp]

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.


Adriano.