Re: enq: TT contention / recyclebin

It's a pity about having to clear the recyclebin before finding out exactly what the problem was - but getting the system to behave as soon as possible is usually far more important thatn understanding the cause (as far as management and users are concerned, at least).

You haven't said which version of Oracle - there was a fairly old version of Oracle where something like this could happen because Oracle could make a mistake when choosing whether to record the tablespace-relative file number or absolute file number.

Did you notice any pattern in the multiple copies - was it always the same number of copies or suspiciously like a multiple of an interesting common factor (like the number of files in the tablespace .... sometimes the internal code has some very silly mistakes.)

If the anomaly comes back it might be a good idea to be create a table to hold a copy of the results from that query against the sys tables that reports the recyclebin and a table to hold a copy of recyclebin$.

Regards
Jonathan Lewis

On Sun, 5 Jun 2022 at 03:53, blair storminson <blair.storminson_at_yahoo.com> wrote:

> Thankyou most kindly for that information.
>
> But if recylebin$ is each extent, would I still *not* expect to see
> duplicates for each file/block_id (aka extent) ?
>
> SQL> select file_id, block_id, count(*)
> 2 from dba_free_space
> 3 where tablespace_name = 'TS1'
> 4 group by file_id, block_id
> 5 having count(*) > 1
> 6 order by 1,2;
>
> 3131 rows selected
>
> Maybe by the time it gets to dba_free_space we're just counting multiple
> dropped segments that at some stage occupied the same space?
>
> Management couldnt wait for more analysis :-( and we went ahead and purged
> the recyclebin, but I am sure it will be back soon enough so I can do more
> research
>
> B.
>
>

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