| |
 |
|
Oracle Tips by Burleson |
Monitoring Deferred Rollback Segments
If a rollback segment is taken offline, its transactions may be
placed in a temporary segment in the rollback segment’s tablespace.
These temporary segments are referred to as deferred rollback
segments. The following SQL code will list any deferred rollbacks in
your 7.x , 8.x or 9.x database:
SELECT segment_name, segment_type, tablespace_name
FROM sys.dba_segments
WHERE segment_type = 'DEFERRED ROLLBACK';
Example output from the preceding select statement:
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------ ----------------- ---------------
RBK1 DEFERRED ROLLBACK USERS
Under Oracle7, if a rollback segment is taken offline, its status
will be changed to PENDING OFFLINE, and it will be taken offline as
soon as its pending transactions are complete. The preceding SELECT
statement could be used to determine if any of these active
transactions are in a deferred state. To determine if a rollback
segment under Oracle7 has outstanding transactions, the following
SELECT statement is used.
SELECT name, xacts 'ACTIVE TRANSACTIONS'
FROM v$rollname, v$rollstat
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. |
