Re: How can I get the number of free blocks?

Uwe Goldhammer <106111.1074_at_compuserve.com> wrote:

>We use a Oracle7 database with 100 table (size of database is 20 GB).
>But only three tables are very big (1.6 to 6.0 GB).
>We use big initial extents because these tables are 75..100% filled.
>Storage Parameters:
> initial extent 1000M
> next extent 200M
> pctincrease 0
>How can I get the number of free blocks in the big extents?
>The data dictionary view DBA_FREE_SPACE tells me the free
>space in a tablespace, not in a extent.
>The view ALL_TABLES is usable only after analyzing the tables,
>which takes for the big ones 7 to 8 hours a table.
>Is there another way to get this information?
>Regards
>Uwe

If you don't need an exact count, use 'estimate statistics' instead of 'compute statistics'. It will give you an approximation that is very close to the actual number of free blocks, and estimate is MUCH faster than compute.

However, be aware that in some versions of Oracle (generally before 7.2), estimate statistics can be buggy and sometimes cause strange results, particularly in selects involving table joins.

If you don't want to trust 'estimate statistics', then you will have to follow up with either a 'compute statistics' or 'delete statistics'.

My own gut feeling is that estimate is less risky on large tables, because the percent of variance between the estimated and actual stats becomes smaller the larger the table.

---
Bolen Coogler
DBA, BellSouth Information Systems