| |
 |
|
Oracle Tips by Burleson |
Monitoring Rollback Current
Usage
To identify which users are using which rollback segments, run the
script in Source 11.25. The report generated shows the Oracle
Process ID, the System Process ID, and the rollback segment in use.
Listing 11.21 shows an example of output from an active rollback
report.
SOURCE 11.25 Example of SQL script to generate active rollback
report.
rem Name : TX_RBS.SQL
rem Purpose: Generate a report of active rollbacksrem Use : From
SQL*Plus
rem History:
rem Date Who What
rem Sept 91 Lan Nguyen Presented in paper at IOUG
rem Walter Lindsey
rem 5/15/93 Mike Ault Added Title80, sets and output
rem 1/04/97 Mike Ault Verified against 7.3
rem 5/16/99 Mike Ault Verified against Oracle8i
rem 10/14/01 Mike Ault Verified against Oracle9i
rem reformated added curext, curblk
rem*************************************************************
COLUMN name FORMAT a10 HEADING "Rollback|Segment"
COLUMN pid FORMAT 99999 HEADING "Oracle|PID"
COLUMN spid FORMAT 99999 HEADING "Sys|PID"
COLUMN curext FORMAT 999999 HEADING "Current|Extent"
COLUMN curblk FORMAT 999999 HEADING "Current|Block"
COLUMN transaction FORMAT A15 Heading 'Transaction'
COLUMN program FORMAT a10 HEADING 'Program'
SET PAGES 56 LINES 80 VERIFY OFF FEEDBACK OFF
START title80 "Rollback Segments in Use"
SPOOL rep_out\&db\tx_rbs
SELECT
r.name, l.Sid, p.spid,
NVL(p.username, 'no transaction') "Transaction",
p.program "Program",
s.curext,s.curblk
FROM
v$lock l,
v$process p,
v$rollname r,
v$rollstat s
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. |
