Re: oracle datamart - partioned views

Well folks,
I'm still trying to clear out my free extents. The problem is in the fet$ table.

Creating an extent means deleting an entry from fet$ with the SQL:   delete from fet$ where file# = :1 and block# = :2 and ts# = :3

But fet$ is clustered on ts#, has no other indexes, and I created all my partitions in the same tablespace.
I've got 25,000 entries under a single hash cluster key, so this delete statement is averaging 1 second
per execution ----

Seems like I'll be waiting for about 7 hours to clear the mess - perhaps drop tablespace would be a better bet.

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article <01bcd4f2$5af71e50$294b989e_at_WORKSTATION>...
>
> Steve,
> Be reasonable - dropping a 4,000 partition table is not something
 you
> would do in a real datawarehouse,
> and when you do you will be dropping at least 4,000 x N (where N-1
 is
> the number of indexes) segments.
> Think what this does for seg$, ts$, fet$ and uet$ activity.
>
> But I do sympathise. In a fit of testing I've just dropped a few
> partitioned tables and left myself
> with 25,000 entries in USER_FREE_SPACE - I'm having a problem now
> creating a simple table -
> 18 CPU minutes and waiting ... presumably Oracle is having some
> trouble deciding which extents
> to coalesce.
>
>
Received on Thu Oct 09 1997 - 00:00:00 CEST