Have you ever thought about the ROLLBACK process? And have you asked yourself how you can undo the modifications that occurred in some fields? Even though the old values ​​have been changed to the new values.

The scenario in brief is that when making modifications to the data in the database, Oracle stores the old values ​​in the Undo Tablespace before they are changed with the new values.

This scenario gives us the opportunity to undo the operations whenever we need to, provided that the old values ​​are still present in the Undo Tablespace

Since it is not conceivable that these values ​​will remain Tablespace exists indefinitely, so it ultimately has a limited capacity and a limited information retention period that the database administrator configures as he sees fit. Through this scenario, we can also perform the Flashback Queries process if the old values ​​are available. We will talk about Flashback Queries later. But in general, it is a query. About values ​​but in the past. The Undo Tablespace is managed by Locally Managed Tablespace and Automatic Extent allocation. At the moment the process starts, an Undo Segment is automatically allocated and created to store the values ​​that change in the database by this process, so that one Undo Segment is allocated for each process, but this Segment can serve a number of processes. When the Extent is full, it is transformed For the next one in the same Segment so that there are at least two Extensions in each Segment, while the maximum depends on the DB Block Size. If all Extensions in the Undo Segment are filled, the Extent is rewritten starting from the beginning, or a new Extent is requested. There may be more than one Undo Tablespace in a single database, but no more than one Undo Tablespace can be working at the same time. The operation of the Undo Tablespace in the database is determined by the variable Undo_Tablespace.

SHOW PARAMETER UNDO_TABLESPACE; 

To create a new Undo Tablespace.

CREATE UNDO TABLESPACE ORCLUNDO DATAFILE

'D:\oracle\product\01.1.0\oradata\ORCL\ORCLUNDO. DBF'

SIZE 200M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; 

Now the database contains two Undo Tablespaces but only one is working which is UNDOTBS1 as shown above.

But you can change the Undo Tablespace that is working in the database from UNDOTBS1 to ORCLUNDO.

ALTER SYSTEM SET UNDO_TABLESPACE='ORCLUNDO' SCOPE=BOTH;

Oracle Database 10g provides an automatic management option for Undo Tablespace, which is Automatic Undo Management, and it is configured by the variable

UNDO_MANAGEMENT so that it takes the value AUTO, while the manual management option Manual Undo Magamenet is an expensive option and requires more work from the database administrator.

The Automatic Undo Management option reduces the burden on the database administrator so that his management is only at the Tablespace level. The database administrator is waiting for the appropriate space to be prepared for the Undo Tablespace according to the information that will be stored. It is also necessary to prepare the Undo Retention period for the data in the Undo Segment using the variable UNDO_RETENTION which takes a value that is the retention period in seconds. The default for this variable is to take the value 0, which means Automatic, meaning that it tries to keep the information until it expires, provided that the information is kept for at least 15 minutes, but another value can be specified, which is the retention period in seconds.

SHOW PARAMETER UNDO_RETENTION;

In general, Undo information, i.e. the information in the Undo Segments, is divided into three cases: -

1- Uncommitted Undo Information: This is the information that has not been installed yet because the operations are still ongoing. This type of information cannot be deleted or rewritten.

2 - Committed Undo Information: We do not need it for continuous operations, but because the retention period has not yet ended "Unexpired", we keep this type of operations as much as possible

Unless this leads to the failure of some operations due to the lack of space in the Undo Tablespace, in this case this information is rewritten,

But the database administrator may initialize the Undo Tablespace. So that we can ensure that it is not erased or rewritten by using the Guaranteeing Undo Retention option.

ALTER TABLESPACE ORCLUNDO RETENTION GUARANTEE;

This option is of course only available for Undo Tablespace.

This way we ensure that information that has not expired will not be deleted even if it leads to some operations failing due to lack of space in Undo Tablespace.

3 - Expired Undo Information: We do not need it for continuous operations, and its retention period has expired so we can rewrite it whenever we need space in Undo Tablespace.

One of the problems that occur frequently and that the database administrator must take into account is: -

1- Undo Tablespace Space Error: The database administrator must monitor the Undo Tablespace space. Operations that do not find space in the Undo Tablespace

are given the error message (ORA-01650: unable to extend rollback segment).

2- Snapshot too old Error: This error appears when executing a query that requires Undo Information that has been deleted and rewritten,

Therefore, the database administrator must take into account the appropriate retention period UNDO_RETENTION as well as the appropriate space while taking into account Guaranteeing Undo Retention.

The UNDO can be inquired about using:

DBA_UNDO_EXTENT

V$UNDOSTAT