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:
Begin the process by running the script $ORACLE_HOME/rdbms/admin/utlbstat.sql.
Select the event you want to monitor or the length of time you want the monitor to run.
End the process by running the script $ORACLE_HOME/rdbms/admin/utlestat.sql.
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.