Oracle: Monitoring databases

According to the Orace DBA Handbook, at a minimum, you should be monitoring for the following items in each instance of Oracle that you support:

1. Free space in all tablespaces
2. Rate of change in free space by tablespace
3. Total space usage by temporary segments at any one time.
4. Sizes and number of extents for rollback segments
5. Number of extents for all segments

The book describes setting up a new instance to monitor all other instances. This provides benefits such as:

1. Monitoring activities can be coordinated across multiple databases.
2. Monitoring activities will not affect teh space usage or performance of the systems that they're monitoring.

I'm not going to go into the details of setting up the monitoring database. I'm more intested in making sure I have the sql statements required to get the data. Thusly:

Free space in all tablespaces

select tablespace_name,

   count(blocks),

   sum(blocks),

   max(blocks)

from dba_free_space

group by tablespace_name;

Number and size extents for all segments

select tablespace_name,

   owner, 

   segment_name,

   segment_type,

   extents,

   bytes/1024 "K"

from dba_segments

where extents > 9

or lower(segment_type) = 'rollback';