| |
 |
|
Oracle Tips by Burleson |
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.
REM
REM FUNCTION: create views required for rbk1 and rbk2 reports.
REM
REM
CREATE OR REPLACE VIEW rollback1 AS
SELECT
d.segment_name, extents, optsize, shrinks,
aveshrink, aveactive, d.status
FROM
v$rollname n,
v$rollstat s,
dba_rollback_segs d
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
