When monitoring storage, you need to pay attention to two separate areas: objects that are going to run out of room or extents in the near future, and total growth over time. You worry about objects in the short term so that you can avoid having your applications fail. You worry about growth in space over time so that you can project when you will need to acquire more disk space.

The following script identifies segments that are getting close to running out of contiguous free space for a NEXT extent:

SELECT owner, 
s.tablespace_name, 
segment_name, 
s.bytes, 
next_extent, 
MAX(f.bytes) largest 
FROM dba_segments s,dba_free_space f 
WHERE s.tablespace_name = f.tablespace_name(+) 
GROUP BY owner,s.tablespace_name,segment_name,s.bytes,next_extent 
HAVING next_extent*2>max(f.bytes) 
/

The following script identifies segments that are getting close to their MAX-EXTENTS value:

SELECT owner,tablespace_name,segment_name,bytes,extents,max_extents 
FROM dba_segments 
WHERE extents*2 > max_extents 
/

The following scripts store information about the size and number of extents of objects in the database. The scripts create historical tables to store information about tablespaces and segments:

CREATE TABLE dba_tablespace_history 
(
     timestamp                DATE, 
     tablespace_name          VARCHAR2(30), 
     num_of_files             NUMBER, 
     num_of_blocks            NUMBER, 
     num_of_bytes             NUMBER 
)
PCTFREE 0 
TABLESPACE tools 
STORAGE (INITIAL 393216 
NEXT 196608 
PCTINCREASE 0);

CREATE TABLE dba_segments_history 
( 
     timestamp               DATE, 
     owner                   VARCHAR2(30), 
     segment_name            VARCHAR2(30), 
     partition_name          VARCHAR2(30), 
     segment_type            VARCHAR2(17), 
     tablespace_name         VARCHAR2(30), 
     bytes                   NUMBER, 
     blocks                  NUMBER, 
     extents                 NUMBER 
) 
PCTFREE 0 
TABLESPACE tools 
STORAGE (INITIAL 1966080 
NEXT 983040 
PCTINCREASE 0);

The following script collects current information about tablespaces and segments:

INSERT INTO dba_tablespace_history 
SELECT TRUNC(sysdate), 
     tablespace_name, 
     count(*), 
     sum(blocks), 
     sum(bytes) 
FROM dba_data_files 
GROUP BY TRUNC(sysdate),tablespace_name;

INSERT INTO dba_segments_history 
SELECT TRUNC(sysdate), 
     owner, 
     segment_name, 
     partition_name, 
     segment_type, 
     tablespace_name, 
     bytes, 
     blocks, 
     extents 
FROM dba_segments;

The following script creates a history table to store information about Oracle tables. Note that some of the columns are only populated after a table has been analyzed. We feel that this information is invaluable. The blocks column allows you to track the highwater mark for used space; this will give you a more accurate picture of table growth than the bytes column from the DBA_SEGMENTS view.

CREATE TABLE dba_tables_history 
( 
     timestamp               DATE, 
     owner                   VARCHAR2(30), 
     table_name              VARCHAR2(30), 
     num_of_rows             NUMBER, 
     num_of_blocks_u         NUMBER, 
     num_of_blocks_f         NUMBER 
) 
PCTFREE 0 
TABLESPACE tools 
STORAGE (INITIAL 786432 
NEXT 393216 
PCTINCREASE 0);

The following script populates dba_tables_history :

INSERT INTO dba_tables_history 
SELECT TRUNC(sysdate),owner,table_name,num_rows,blocks,empty_blocks 
FROM dba_tables;

This last set of scripts creates a history table for indexes. Note that we grab the information from the INDEX_STATS view. This table is populated one index at a time after an index has been analyzed with the VALIDATE STRUCTURE option. This is because the information available in INDEX_STATS is more complete than that found in DBA_INDEXES, even after the index has been analyzed using the normal strategy.

CREATE TABLE dba_index_history 
( 
     timestamp                DATE, 
     owner                    VARCHAR2(30),
     height                   NUMBER, 
     blocks                   NUMBER, 
     name                     VARCHAR2(30), 
     lf_rows                  NUMBER, 
     lf_blks                  NUMBER, 
     lf_rows_len              NUMBER, 
     lf_blk_len               NUMBER, 
     br_rows                  NUMBER, 
     br_blks                  NUMBER, 
     br_rows_len              NUMBER, 
     br_blk_len               NUMBER, 
     del_lf_rows              NUMBER, 
     del_lf_rows_len          NUMBER, 
     distinct_keys            NUMBER, 
     most_repeated_key        NUMBER, 
     btree_space              NUMBER, 
     used_space               NUMBER, 
     pct_used                 NUMBER, 
     rows_per_key             NUMBER, 
     blks_gets_per_access     NUMBER 
) 
PCTFREE 0 
TABLESPACE tools 
STORAGE (INITIAL 1966080 
NEXT 983040 
PCTINCREASE 0);

The following script analyzes the index and populates the history table. It is passed two parameters, owner and index_name. You must run this script for every index for which you want to analyze and store information.

ANALYZE INDEX &owner..&index_name VALIDATE STRUCTURE; 
INSERT INTO dba_index_history 
SELECT TRUNC(sysdate), 
       '&owner', 
       height, 
       blocks, 
       name, 
       lf_rows, 
       lf_blks, 
       lf_rows_len, 
       lf_blk_len, 
       br_rows, 
       br_blks, 
       br_rows_len, 
       br_blk_len, 
       del_lf_rows, 
       del_lf_rows_len, 
       distinct_keys, 
       most_repeated_key, 
       btree_space, 
       used_space, 
       pct_used, 
       rows_per_key, 
       blks_gets_per_access 
FROM index_stats;

UTLBSTAT and UTLESTAT are a paired set of scripts provided by Oracle to collect statistics about the activity of the database over time. You typically use these scripts to monitor how the instance reacts to a given stimulus, perhaps a benchmark or a periodic monitoring. The scripts work as follows:

UTLBSTAT creates a set of tables that store current copies of many of the dynamic performance data dictionary views. When UTLESTAT is run, it compares the stored values with the then current copies and generates a report. The report is stored as $ORACLE_HOME/rdbms/admin/report.txt.