Re: Problem with LMT and ASSM

Markus,
when you INSERT, do you do it with /*+ APPEND*/? if so (it looks from your post, but just wanted to verify), then
the space deleted will not get used, as it is below the HWM, hence not used by the insert in direct mode.
The best way to do it, if possible, is to partition the table in a way that allows you to DROP the partitions instead of deleting the records. This will really free the space used, and hence let the INSERT /+ append*/ reuse it.

rgds

On 6/27/06, Markus Kuehn <Markus.Kuehn_at_lbs-bw.de> wrote:
>
>
> Hello members,
>
> sorry, forgot the subject.
>
> We have a problem with locally managed tablespaces and segment space
> management auto on 9.2.0.6 on AIX 5.2. The problem follows:
>
> We created a table in a tablespace with extent management local
> autoallocate and segment space management auto. Every day about 150.000
> records are inserted into this table. Records which are older than 90 days
> are deleted from the table. The delete-job runs at 17:00, the insert-job
> runs at 00:30, so that they are seperated from each other. No updates are
> taking place in this table. The first inserts created 64 extents with 128
> blocks and 1 MB each. After those extents went full, 120 extents with 1024
> blocks and 8 MB each were created. Now, as they went full too, extents
> with
> 8192 blocks and 64 MB each are created. Due to the deletes the lower
> extents from extent id 0 to extent id 90 are freed but are not reused.
> Instead new extents are created. This will lead to a tablespace covering
> all available disk-space, which is not really satisfying. An "alter table
> move" to a different tablespace and "alter tablespace move" to the
> original
> tablespace reduces the number of used extents. But after some time we run
> in the same problem again.
>
> Anyone out there has a solution or work-around for this type of situation.
>
> Regards
> Mit freundlichen Grüßen
>
> Markus Kühn
> LBS Landesbausparkasse Baden-Württemberg
> Abteilung OI
> Gruppe Datenbanken und Konfigurationsmanagement
>
> Jägerstraße 36, 70174 Stuttgart
> Postfach 10 60 28, 70049 Stuttgart
>
> Siegfried-Kühn-Str. 4, 76135 Karlsruhe
> Postfach 14 60, 76003 Karlsruhe
>
> Telefon 07 11 / 1 83 - 2915
> Fax 07 11 / 1 83 - 492915
> E-Mail Markus.Kuehn_at_LBS-BW.de
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
> Die LBS Baden-Württemberg verwendet aktuelle Virenschutzprogramme. Wir
> haften nicht für Schäden, die dem Empfänger durch diese E-Mail entstehen
> könnten. Bitte haben Sie Verständnis, dass wir die rechtliche
> Verbindlichkeit für den Inhalt dieser E-Mail ausschließen. Sind Sie nicht
> der beabsichtigte Empfänger dieser E-Mail, teilen Sie uns dies bitte mit
> und löschen die E-Mail. Vielen Dank.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l