ss

Oracle Tips by Burleson

Monitoring Database Rollback Segments Using V$ and DBA_ Views

Rollback segments and the new Oracle9i UNDO segments must be monitored. Though their tablespace area is monitored through the freespace and extents reports shown in previous sections, it would be helpful to have a report just for rollback segments to present rollback-related data in one convenient location. Even in Oracle9i, information on UNDO usage is helpful to the DBA in tuning the Oracle UNDO tablespace. The same views as with Oracle7, Oracle8, and Oracle8i are used to monitor UNDO segments in Oracle9i.

Monitoring Rollback Usage and Statistics

Unfortunately, the DBA_ROLLBACK_SEGS view is just too large to allow a single report to cover all of the parameters it shows. Therefore, two views and two reports are required to adequately cover the DBA_ROLLBACK_SEGS view and the monitoring of rollback segments. The scripts in Source 11.22 create two views, ROLLBACK1 and ROLLBACK2, both based on the V$ROLLSTAT and V$ROLLNAME views, which are very important for monitoring rollback activity. The DBA_ view, DBA_ROLLBACK_ SEGS, is based on these two tables. In Oracle9i, a new V$ view was added to allow monitoring of the UNDO segment usage statistics; this new view is called V$UNDOSTAT.

SOURCE 11.22 SQL scripts to generate ROLLBACK1 and ROLLBACK2 views.

See Code Depot