Re: Space used in extents

In article <55pvn3$p01_at_romeo.logica.co.uk> howdena_at_logica.com writes:

> I need to be able to tell how much free space is left in a given tablespace.
>
> I know there is a data dictionary view dba_free_space, but this only shows the
> amount of unallocated space in the tablespace i.e. not given to extents.
>
> We have designed the tablespaces to give the objects in them large initial extents
> and much smaller next extents in an attempt to get all our data goes into the first
> extent (a tuning tip).
>
> How can we find out how much of an extent is used WITHOUT putting statistics on the
> table (as this is slow and will affect the optimisation method)? The database must
> be up 24hrs a day so no non-operational time is available.

You should take a look at the extent's tables and how much space they are using.

You may try

select count (distinct substr(rowid,15,4)||substr(rowid,1,8))   from USER.TABLE

this query will give you the number of blocks containing at least one row. But, for sure, this query will do a full table scan on your table.

You should also take a look at the highwater mark of the table, for this is the amount of blocks read from disk.

There's a lot of useful stuff related to space allocation and usage in Cary V. Millsapp's white paper 'Oracle Server Space Managment' at

http://tiburon.us.oracle.com/odp/public/library/cr/html/cr_white.html

Hope this helps.

Axel Schlueter
schluete_at_informatik.uni-hannover.de
Universitaet Hannover, Germany Received on Wed Nov 06 1996 - 00:00:00 CET