Data Dictionary Information on Undo


The means by which to locate undo segment information in the data dictionary is discussed in this section.

  • V$UNDOSTAT Provides statistics on undo data. You can use this view to determine how well the system is working. Each row in the view provides statistics collected in the instance at 10 minute intervals. You can use this view to help to determine how much undo space to estimate for your current workload. The database uses this view somewhat differently; it uses the view to tune undo usage in the system. The view is available for both automatic and manual undo management mode.

    The most recent row in the dynamic view returns the time since the interval started, which is typically less than 10 minutes.


  • V$ROLLNAME Provides the names of all the online undo segments in the database.

  • V$ROLLSTAT Provides statistics on all the online undo segments in the database. It can be joined to v$rollname with the USN column.

  • V$SESSION Lists all the active sessions in the database.

  • V$trANSACTION Lists all the active transactions in the database. If you want to check the use of an undo segment by a currently active transaction, you would join the v$transaction view to the v$session view on V$SESSION.saddr and V$trANSACTION.ses_addr to get the values of XIDUSN, UBAFIL, UBABLK, and USED_UBLK, or how many blocks that transaction is using in the undo tablespace.

    For example:

     SELECT b.username, a.xidusn, a.ubafil, a.ubablk, a.userd_ublk FROM V$TRANSACTION a, V$SESSION b WHERE a.ses_addr = b.saddr; 

  • DBA_ROLLBACK_SEGS Provides information about all the undo segments in the database. This is the only place where you can obtain information about undo segments currently offline. The OWNER column specifies the type of undo segment in question. SYS refers to private undo segments; PUBLIC refers to a public undo segment.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net