Rjamya,
I checked 3329096.8 which exactly described our problem. I'll run
segment_fix_status and see if it does anything good.
Regards
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@(protected)
Amtsgericht Stuttgart HRA 12924
Amtsgericht Karlsruhe HRA 4548
rjamya
<rjamya@(protected)
> An
Markus.Kuehn@(protected)
27.06.2006 17:36 Kopie
Thema
Re: RE: Antwort: RE: Problem with
LMT and ASSM
My memory is bad, but there was an issue with bitmaps (as in lmt) not
reflecting correct fullness of segments in 9204 and related versions.
Could it be that? Metablink had a fix for that check doc id 3329096.8.
Now given that it says the issue is fixed in 9206, I'd still check if
dbms_repair.segment_fix_status can help.
should be easy to test ... after you purge, get table space info using
dbms_space. Run above mentioned code and check the space again. If you
find a difference, the bug isn't fixed.
The process is supposed to be non-corruptive. I'd check on a test db first.
Raj
On 6/27/06, Markus Kuehn <Markus.Kuehn@(protected):
> Joel,
>
> other segments are in this tablespace. These segments are not very large
so
> they are using small extents (128 blocks 1 MB each), which are reused.
> Thinking about your answer it seems to me, that maybe I hit a bug ?! I'll
> think I'll give the tablespace with uniform extents a test ... and I'll
> open a service request. Let's see what ORACLE says about this.
>
> Regards
>
> 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@(protected)
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
>
> Wittenmyer Joel -
> CO
> <WITTENMYERJ@(protected)
An
> .com> 'Markus Kuehn'
> <Markus.Kuehn@(protected)>
> 27.06.2006 16:48
Kopie
>
>
Thema
> RE: Antwort: RE: Problem with LMT
> and ASSM
>
>
>
>
>
>
>
>
>
>
> Just a thought. Since Oracle allocates ever larger extent sizes based on
> the amount of current space, it may not be reusing the smaller extents
for
> the same segment. It might be insisting on larger extents because it
> thinks
> it will need ever larger extents. Do any other segments live in this
> tablespace? Are they using the smaller extents (assuming that they are
not
> also very large)? I'm wondering if Oracle would reuse the extents if the
> tablespace were uniform rather than autoallocate.
>
> -----Original Message-----
> From: Markus Kuehn [mailto:Markus.Kuehn@(protected)]
> Sent: Tuesday, June 27, 2006 9:35 AM
> To: Wittenmyer Joel - CO
> Cc: oracle-l@(protected)
> Subject: Antwort: RE: Problem with LMT and ASSM
>
> Hi Joel and Salem,
>
> no APPEND-hint in the insert statement and partitioning is not an option
> due to licensing costs. Any other hints ??
>
> Regards
>
> 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@(protected)
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
>
> Wittenmyer Joel -
> CO
> <WITTENMYERJ@(protected)
An
> .com> "'salem.ghassan@(protected)'"
> <salem.ghassan@(protected)>,
> 27.06.2006 16:28 Markus.Kuehn@(protected)
>
Kopie
> oracle-l@(protected)
>
Thema
> RE: Problem with LMT and ASSM
>
>
>
>
>
>
>
>
>
>
> My first thought also. And if you use 'parallel append' you can actually
> hear the space being chewed up J as each parallel process gets it's own
> extent to devour.
>
>
> From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]
> On Behalf Of Ghassan Salem
> Sent: Tuesday, June 27, 2006 9:26 AM
> To: Markus.Kuehn@(protected)
> Cc: oracle-l@(protected)
> Subject: 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@(protected):
>
> 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@(protected)
>
> 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
>
>
>
>
>
> 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.
>
>
>
>
> 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
>
>
>
--
----------------------------------------------
Got RAC?
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
