How do I effectively monitor and notify OS diskspace utilized by Oracle tablespaces?

there are 2 problems:
- space usage in OS
- space usage in datafiles
as i know you can monitor space usage in OS only by OS commands - "df - k" in unix systems
if you set AUTOEXEND clause of datafiles to OFF and set exact sizes of datafiles you do need need to check space usage in OS. You estimate or jus make decision how many space can each tablespace ocupy and set sizes of datafiles to exact values and turn autoextend off.
monitoring space usage within database - use dba_free_space. just ensure that all your tablespaces are in "local management mode with the same size of extent". if is'n you can have many free space in tablespace but only small pieces and if some table require biger piece, new space will be allocated (if autoextent on)
the thitd problem is that if you only delete some data, space remains allocated to the table and only new data of the table can use it. to release space within tablespace can be done only by droping table, or as you mentioned by deleting partition.
so i recommend you:
1. set filesizes to maximal values you want have and turn autoextent off
2. convert tablespaces to have the same extent size if isn't (9i and 10g has the same extent by default, 8i not)
3. set up jobs, you mentioned, they will scan dba_free_space and drop old partitions

Thanks konektor,

With the OLAP setup that we have, we cannot set autoextend off as we are inserting about 2 millions records per second in our partiationed table. So we have to keep autoetend on.

Instaead of dropping old partitions, I will truncate old partitions with DROP STORAGE clause. That will free up space in the Table Space. The problem is that even if the space is de-allocated in the TableSpace, the datafile for this table space will still be showing the biggest datafile size. and so my loop will remain in endless iteration.

If it helps then do you want me to post the code that I have written so far?

Thanks

RD

1.      Free.sql
--
-- free.sql
--
--   To verify free space in tablespaces
--   Minimum amount of free space
--   document your thresholds:
--   <tablespace_name> = <amount> m
--

SELECT  tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m
, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

2.      Space.sql
--
-- space.sql
--
-- To check free, pct_free, and allocated space within a tablespace
--
-- 11/24/98

SELECT tablespace_name, largest_free_chunk
     , nr_free_chunks, sum_alloc_blocks, sum_free_blocks
     , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
       AS pct_free
FROM ( SELECT tablespace_name
            , sum(blocks) AS sum_alloc_blocks
       FROM dba_data_files
       GROUP BY tablespace_name
     )
   , ( SELECT tablespace_name AS fs_ts_name
            , max(blocks) AS largest_free_chunk
            , count(blocks) AS nr_free_chunks
            , sum(blocks) AS sum_free_blocks
               FROM dba_free_space
               GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name

Thanks for your response schwertner and konektor....

Let me pose the question in a simpler way... In a tablespace that is set to autoextend, the datafile fro this table space will grow. Now if I truncate some table from this Tablespace with DROP STAORAGE clause, I gainback the space in the table space but the size of the data fiel will remain the same. I mean after performaing truncate with DROP staorage, ecven though I get the space back in tablespace, I do not get the space back on OS for this data file. How can I tackle this problem because my alertrunning at 85% of the OS disk space will keep coming up.

Thanks

This is not so easy.
There is a thing called "High Watter Mark" in Oracle.
You can easy reclaim the space above the HWM
and it is described here:
http://www.oracleadvice.com/Tips/dfhwm.htm

The taugh problem is to free the blocks lies under HWM.

In Oracle9i and earlier database versions, you can reclaim the free space by dropping the table, re-creating it, and then reloading the data or by moving the table to a different tablespace, using the ALTER TABLE MOVE command. Both of these processes must occur with the table offline. Alternatively, you can use online table reorganization, but that requires at least double the space of the existing table.

With Oracle Database 10g, you can now shrink segments. The process of shrinking a segment includes the following phases:

Segment data is compacted.
High-water mark (HWM) is pushed down.
Unused space is released back to the tablespace containing the segment.

Segment shrink is an online, in-place operation. It does not require extra disk space to be allocated. Because a shrink operation may cause ROWIDs to change in heap-organized segments, you must enable row movement on the corresponding segment before executing a shrink operation on that segment.

To shrink segments, use Oracle Enterprise Manager 10g or the SHRINK SPACE clause of the ALTER command.