Re: Monitoring Tablespace Capacity

Kurt,

The space used by Oracle is split up into blocks. The DBA_FREE_SPACE view shows free extents. These are continuous areas of free blocks that Oracle can use to extend existing data structures. Each record in DBA_FREE_SPACE corresponds to an area of free space available within a tablespace.

You can calculate the total amount of free space in a tablespace using DBA_FREE_SPACE and you can also gauge the amount of fragmentation. You might have plenty of free space in your tablespace but still be unable to extend tables if the available space is split up into very small fragments (no fragment is big enough to provide the next extent for your table).

You can join adjacent, free extents together to make bigger free extents using the ALTER TABLESPACE ... COALESCE command. This is like defragmenting the free space on a disk drive.

I don't think you can defragment used space easily like this. If your free space is divided up by many small, used extents, you might need to use the IMP and EXP commands to defragment the entire tablespace.

I hope this is of some help to you.

Richard

Kurt Graves <kjgraves_at_tel.gte.com> wrote in message news:3A4B85E4.DD1F9A05_at_tel.gte.com...
> I would like to monitor the amount of available space in a tablespace.
> Ideally, I would like to be able to do this with a ksh script executed
> from
> Crontab. I'd like to set up a '% free' threshold and output only if
> it's exceeded.
>
> If I understand correctly, one must take into account the extent size,
> autoextend option for datafiles within a tablespace, etc.. I have been
> looking at the following
> views but am uncertain in interpreting what they tell me.
> dba_free_space, dba_extents, dba_data_files
>
> We are running ORACLE 8.0.5 on AIX 4.3.2.
>
> If anyone is doing this sort of thing, I will appreciate your ideas.
>
> Thanks,
>
> Kurt
>
Received on Tue Jan 02 2001 - 02:34:27 CET