At 09:32 AM 1/16/96 -0600, you wrote:
>Is there a way to force ORACLE to coalesce the free space?
>
>I know in the 7.0 you can set event to do that. How about
>the 7.1 or 7.2?
>
>I experienced some weired problem. When I droped an index
>I can not recreate that index using same creating scripts.
>( Off cause the data itself only has little change ).
>But after a while let's say 30 minutes I can create the
>index again using the same scripts.
>
>I report that to ORACLE, guess what? What they tell me is
>I should not drop the index and recreate it normally if I
>really want to do that I have to leave at least 50% indexes
>tablespace free.
>
>There are Two problem involved here, one is the RDBMS does not
>coalesce free space in time, the other is sometimes RDBMS will leave
>a temporary segment in the index tablespace( NOT the temp tablespace,
>sitting there until next coalesce event happens)!
>Off cause the representative I talked with did not admit that is a
>Oracle Bug. We have some batch job running every night need to reload
>some tables I drop the index recreate it after loaded. Now I have to
>get up every night to rerun the scripts.
>
Dear Michael,
It really is an Oracle-bug that doen not exist in realease 7.1.6 anymore, at
least OTS 1n Holland told me so.
The SMON process deletes the space from drop table/index/primary key etc.
But that does not happen immediately.
It can take about half an hour before the deleted space in your tablespace
is really empty.
I suppose you had an ORA-1547 error (no space for extent in tablespace).
Invoking commits into your script does not help either.
What yo can do:
1) Go over to release 7.1.6 or higer (and discover new bugs :-)
or
2) Run the create-index script twice (without deletes ofcourse); not a good
solution.
The first time 75% of your indexes is created.
And half an hour later the rest with errors for the already existing ones....
or
3) Do something with initial extent and next extend parameters for your indexes.
And define a special tablespace for your indexes of about 40-50% of your
data-tablespace,
in which you put your indexes etc. So not only space-parameters for the
tables but extra for the indexes.
That helped me at least to overcome that Oracle-bug.
Hans Ackema
Oracle-dba
University of Amsterdam
.although I fear that you can't alter too much.
Received on Tue Jan 16 1996 - 11:57:07 CST
