Rollstat

• Roll Stats Dnd
• Roll Stats 5e

A Rollback Segment is a database object containing before-images of data written to the database. Rollback segments are used to: Undo changes when a transaction is rolled back. Break on today column today noprint newvalue xdate select substr(tochar(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.' ),1,35) today from dual; column name noprint newvalue dbname column owner format a35 column object format a35 column objectname format a35 column 'ID#' format a12 column 'ID' format a12 column 'File Name' format a60 column 'Phy.

Posted December 26, 2017

By David Fitzjarrell

DD03M is an SAP View used to store Generated Table for View data. Below is the documentation, technical details of each fields, ABAP code etc. If you have Oracle database running on your environment, even if you are a developer or linux sysadmin, you might end-up doing some basic DBA tasks on deve.

The UNDO tablespace is one of the workhorses of an Oracle database (TEMP being the other) yet, unlike TEMP, UNDO requires a special tablespace configuration so that the UNDO mechanism operates properly. Conventional wisdom on configuring tablespaces in general has little or no place when it comes to the UNDO tablespace and following such guidelines with the UNDO tablespace can wreak havoc on storage. Let's look into that a bit more.

UNDO is a special mechanism that provides a way for Oracle to do several things:

It's also configured to reuse segment space as it becomes available. This is where conventional tablespace wisdom can bite you if that conventional tablespace/datafile wisdom is to set autoextend on for at least one file in the tablespace. Doing so for other tablespaces can be a good idea. [Monitoring TEMP can help catch runaway statements that consume vast amounts of temporary space and cause the TEMP tablespace to expand to the limits of the available storage; such statements are usually few and far between.] Setting autoextend for any file in the UNDO tablespace is a mistake and possibly a disaster waiting to happen.

Let's look at what is supposed to happen with the UNDO tablespace:

That, of course, is the same mechanism that can throw the dastardly ORA-01555 (snapshot too old) error. With automatic UNDO management this usually isn't an issue but there are some who think that setting autoextend on for the UNDO datafiles will fix that problem. Unfortuately it won't as autoextend doesn't increase the size of the undo segments, just the UNDO tablespace, and if autoextend is on this defeats the mechanism of reusing UNDO space from aged-out UNDO segments. As a result the UNDO tablespace can grow seemingly without bound since Oracle will simply allocated additional file space rather than resuse the expired segments.

Roll Stats Dnd

Oracle does not recommend the use of autoextend on any of the datafiles associated with the UNDO tablespace, which doesn't mean Oracle doesn't allow such a configuration. Examples in the documentation clearly set up autoextend on an UNDO tablespace. According to the documentation: 'Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment.' To me production is not the place to be unsure of the UNDO configuration; that's what the testing database environment is for. Running UNDO with autoextend on, in a load-testing scenario might be a good idea as it could provide very useful information for the setup of a fixed-size UNDO tablespace in the production environment. Letting UNDO grow essentially unchecked is not wise; let the system generate errors for space and address them as the need arises. It's very likely that these errors will be few and require far smaller adjustments than autoextend would generate.

UNDO needs to be checked just like other tablespaces and two views are key to this task: V$UNDOSTAT and V$ROLLSTAT. The following queries report on the status of used UNDO segments, who is using them, and their respective sizes:

This script, or something similar, can help keep track of who is using UNDO segments and how large those segments are. It can also help if it's run during long transactions as any UNDO size errors will report the segment at fault and that can be mapped back to the user, and transaction, causing the problem.

If even more UNDO information is desired the following script will report undo segment size, resizes, extensions, active transactions and a few other bits of information:

Such a report may be very useful during problem investigations.

Roll Stats 5e

The UNDO tablespace should not be configured using autoextend as it intereferes with the normal operation of the UNDO mechanism. If there are regularly occurring UNDO errors then it's very likely the UNDO tablespace is undersized and it should be expanded manually, in known increments, until the errors go away. Of course there will likely be that one transaction that does throw an error on occasion but those should be dealt with on a case-by-case basis; in the case of ORA-01555 errors it may not be the UNDO tablespace at fault so query tuning might be the correct direction to take. Each generated error needs to be evaluated on its own and addressed accordingly; not every error benefits from a generalized 'solution', and it's better to let the space-related errors be thrown so an actual root cause can be determined than to let Oracle 'run wild' with space allocations by setting autoextend on.

Monitoring and managing UNDO space is a bit different than for any other tablespace, and once that is known the task becomes a bit easier. And shortcuts, like using autoextend, can create more problems than they were intended to fix.