Oracle 9i Undo Management


So much to tell about undo management that even this post is not sufficient to explain the concept. Well a small effort to explain the undo concept in Oracle Database 9i. Lets start with the main funda and then we will see how automatic undo management and manual undo management differs. We will also see undo segments and difference between private and public undo segments.

Undo fundamentals

Undo is the rather the most important functionality present in database, without which a database cannot work. undo serves following purpose in database

  • Provide transaction level read consistency of data to all users in database
  • Permits user to rollback or discard the changes that have been made in transaction
  • Provide transaction recovery in case of instance failure while the user is in the middle of transaction

When a user fires a DML (Either update, delete or insert), oracle will fetch the data from disk into database buffer cache for user to change the data also at the same time a copy of the original data is placed in undo segments. This happens for every DML transaction that is executed in database. When a user makes changes to the data, he can verify the change before doing COMMIT. If he thinks that the data change was not correct, he can ROLLBACK the changes back to original. This functionality is provided by undo. The copy of data which was placed in Undo segment will be copied back and any data change will be discarded. This is a very basic principle. The actual transaction is bit complicated.

When a data is fetched into database buffer cache a copy of the same has to be placed in undo segment. Now imagine that there are many users that are working in the database simultaneously and each firing a DML statement needs to place the original copy in undo segment. How the undo segment will be managed amongst so many transactions? To answer this question we need to understand Automatic undo management and manual undo management. Lets see first Auto undo management.

Automatic Undo Management

In case of automatic undo management, we delegate all the burden of undo segment management to oracle. Oracle will take care of creating the required number of segments as and when required. In return, what oracle want is a tablespace from where he can create segments. So we have to provide a undo tablespace which oracle can use to create undo segments and store the original copy of data for user to view. For enabling automatic undo management, we need to set following parameters


For automatic undo management this parameter should be set to AUTO.


Here we provide the name of undo tablespace that we want oracle to use. Undo tablespace is a permanent tablespace that we create (just like other permanent tablespace) and gives it to oracle.


To understand undo retention consider this scenario. User A fetches the data to update, a copy of data is also placed in undo segment in undo tablespace. User B queries for same data and he is given the old copy of data from undo segment (because user A has not yet committed the changes). After few mins, user A commits the changes.

When the data was copied to undo tablespace in one of the undo segment a lock was also placed on that data, which prevents other undo data to overwrite this data. As soon as user commits the data, this lock will be taken off and data can be overwritten.

Imagine that this data gets overwritten quickly and when user B re-queries the data in the same transaction he gets the error ORA-15555 Snapshot too old.

This happened because the undo segment from which user B was querying the data has been overwritten by some other undo segment data. When a user fires a DML oracle will look for free undo segments to put the copy of data and as soon as he finds the free undo segment he overwrites the previous data. However It would be good if oracle retains the data in these segments for some more time even after the user has committed the data and lock is remove. But for how much time should oracle keep this data after commit? This time is decided by this init.ora parameter UNDO_RETENTION. The time is specified in sec. Usually a value of 900 (sec) is seen to be fine.

In the header of undo segment there is a undo segment table. This table contains the records about which transaction is In-active and which transaction is using the undo segment currently. Also the size of undo segment is decided automatically by oracle and is sufficient to support current number of transactions. When a segments get full by data from various transactions, additional space is allocated to the undo segments by allocating extents to the segments. Extents are similar to segment but are allocated automatically if the segment get full. If the extent also gets full, next extent will get allocated. This continues till oracle accommodates current active transactions.

Manual Undo Management

In case of manual undo management a DBA is supposed to create undo segments manually. For understanding this lets first understand system and non-system undo segments and public and private undo segment.

System and non-system undo segments

System undo segment is the one which resides in system tablespace and is created by oracle when you create a database. This undo segment will be used by oracle while updating the data dictionary. A normal user or a DBA is not supposed to use this segment.

non-system undo segment is the one which is created out side system tablespace and which will be used for normal transactions. A non-system undo segment can be pubic or private

Public undo segment and private undo segment

A private undo segment is the one which can be created by initializing the init.ora parameter – ROLLBACK_SEGMENTS. You can specify the name(s) of undo segment which you want to create in this parameter as comma separated names and oracle will create those undo segment when it starts the database. These are called private undo segments because these are acquired by instance explicitly after starting the instance.

Public undo segments are the one that are available in the database pool from undo tablespace that you create.

When configuring the undo management manually, you need to define 2 parameters in init.ora


Based on the values of these parameter, oracle will decide the number of rollback segments required for the proper working of oracle database.

Example if number of transactions during normal operation is defined as 146 in init.ora file and TRANSACTIONS_PER_ROLLBACK_SEGMENT is defined as 18, then oracle needs 8 rollback segment for proper functioning. Oracle then checks if ROLLBACK_SEGMENTS parameter contains 8 rollback segments defined. If not then the difference of the rollback segments are taken from the pool of public undo segments.

So at any given time there are many undo segments and many extents are allocated to each undo segments. The extents are sequential. Example for the first transaction extent 1 will be allocated, for transaction 2 extent 2 will be allocated and so on.

Consider the below figure. In case currently 5 extents has been allocated.

Now if a new transaction comes, oracle will check if any of the extent is free (In-active). Even a single extent can hold more then one transaction. Extents are made of oracle block. But a single oracle block can hold data from only one transaction. Now imaging that Extent 5 is having some space and all other extents are full. In this case new transaction will be allocated to extent 5. As transaction proceeds, the space in the extent will start getting occupied. A stage will come when extent 5 will get full. Not ideally one of the other extent should get freed (In-active) because of completion of some other transaction. But if all the transactions are long running then none of the extent will be freed. In this case oracle will pull a new extent into this “cycle” – an extent 6 as given below.

With that taken place, now we have 6 extents in a cycle. After some time some of the transactions commits and there extents gets freed and can be allocated to other active transactions if required. Again if extents are insufficient a new extent will be pulled. This continuous until the tablespace is full.

For manual undo management, you need to set UNDO_MANAGEMENT=manual

“Optimal” Clause

From the above scenario you can imaging that if the transactions are big enough then the cycle of extents will grow big and can be come unmanageable. To over come this problem there is a clause defined while creating undo segments. This clause is “OPTIMAL” clause. We can define optimal to some value, either in KB or on MB. If we take above example, consider each extent to be of size 1M and we have defined OPTIMAL=5M while creating undo segment. Now untill 5 extents are present in the cycle, no action will be taken. But when there is no space in any of the extent a 6th extent will be pulled. As soon as any extent gets freed it will be removed from the cycle and only 5 extent (equal to size specified by optimal) will be maintained. If some of the extents amongst 5 are free, none of them will be removed, because optimal is specified as 5M. Oracle will always try to maintain the size of segment as 5M. Following is the example for creating rollback segment with optimal parameter


Data Dictionary

You can see the name of rollback segments, tablespace they reside into and status using following data dictionary view.

2 from dba_rollback_segs
3 order by segment_id;

—————————— ———- ———- —————-
_SYSSMU1_1207284872$ UNDO_TBS 1 ONLINE
_SYSSMU2_1207309696$ UNDO_TBS 2 ONLINE
_SYSSMU3_1207980887$ UNDO_TBS 3 ONLINE
_SYSSMU4_1207980890$ UNDO_TBS 4 ONLINE
_SYSSMU5_1207980890$ UNDO_TBS 5 ONLINE
_SYSSMU6_1207984687$ UNDO_TBS 6 ONLINE
_SYSSMU7_1207984689$ UNDO_TBS 7 ONLINE
_SYSSMU8_1207984689$ UNDO_TBS 8 ONLINE
_SYSSMU9_1207984689$ UNDO_TBS 9 ONLINE
_SYSSMU10_1207984689$ UNDO_TBS 10 OFFLINE

—————————— ———- ———- —————-
_SYSSMU11_1207984689$ UNDO_TBS 11 OFFLINE
_SYSSMU92_1204797035$ UNDO_TBS 92 ONLINE
_SYSSMU93_1204797035$ UNDO_TBS 93 ONLINE
_SYSSMU94_1204797035$ UNDO_TBS 94 ONLINE
_SYSSMU95_1204797035$ UNDO_TBS 95 ONLINE
_SYSSMU96_1204797035$ UNDO_TBS 96 ONLINE

If you can see carefully here, segment SYSTEM belongs to SYSTEM tablespace and having ID as 0. This is a system undo segment.

Hope this helps !!


Oracle Database 9i – Basic Architecture


This post is regarding the basic database architecture for 9i. Its been very late to upload this basic stuff, but I realized that it would be an incomplete blog without having even a brief architecture. So here it is.

Below is the figure which gives a overview of “Inside Oracle”.

An Oracle database is a combination of oracle Instance and data files on the file system.

Oracle Database = Oracle Instance + Datafiles

Again Oracle Instance is nothing but Memory architecture and Background processes. Lets start the discussion with Memory architecture first.

Memory Architecture

Oracle database uses memory for its operation. The total memory allocated to the Oracle database can be broadly categorized into SGA (System Global Area) and PGA (Program Global Area).

SGA Contains following data structure

  • Database buffer cache
  • Redo log buffer
  • Shared pool
  • Java pool
  • Large pool (optional)
  • Data dictionary cache
  • Other miscellaneous information

We can also categorized SGA into fixed SGA and variable SGA. When asked about Fixed SGA, AskTom says that “fixed SGA is a component of the SGA that varies in size from platform to platform and  release to release.  It is “compiled” into the database.  The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters.  The size of the fixed SGA is something over which we have no control and it is generally very small.  Think of this area as a “bootstrap” section of the SGA, something Oracle uses internally to find the other bits and pieces of the

Variable SGA contains 4 main components as listed above, those are “Database Buffer Cache”, “Redo Log Buffer”, “Shared Pool” and “Large Pool”. We call it variable SGA because we can alter the size of each of these components manually using ALTER SYSTEM command. The size of each of the components of variable SGA is determined by INIT.ORA parameters. Following are the INIT.ORA parameter for each of the component.

  • Database Buffer Cache – db_block_buffers
  • Redo Log Buffer – log_buffer
  • Shared Pool – shared_pool_size
  • Large Pool – Large_pool_size

We cannot however alter the size of Fixed SGA.

Database Buffer Cache – This is used to hold the data into the memory. When ever a user access the data, it gets fetched into database buffer cache and it will be managed according to LRU (Least recently used) algorithm. Advantages – If a user is requesting data, which gets fetched into the buffer cache, then next time if he ask for same data with in a short period of time, the data will be read from buffer cache and Oracle process does not have to fetch data again from disk. Reading data from buffer cache is a faster operation. Another advantage is that if a user is modifying the data, it can be modified in the buffer cache which is a faster operation then modifying the data directly on the disk.

Redo Log Buffer – This memory block hold the data which is going to be written to redo log file. Why do we need this data? To rollback the changes if the need be. But instead of writing the data directly to the redo log files, it is first written to log buffer which improves performance and then with the occurrence of certain event it will be written to redo log file.

Shared Pool – This contains 2 memory section, 1) Library Cache 2) Dictionary Cache. Library cache hold the parsed SQL statement and execution plans and parsed PLSQL codes. Dictionary cache hold the information about user privileges, tables and column definitions, passwords etc. These 2 memory components are included in the size of shared pool.

Large Pool – If defined then used for heavy operations such as bulk copy during backup or during restore operation.

The total size of SGA is determined by a parameter SGA_MAX_SIZE. Below is the simple calculation of memory sizes.

SQL> show sga

Total System Global Area  577574308 bytes
Fixed Size                   452004 bytes
Variable Size             402653184 bytes
Database Buffers          163840000 bytes
Redo Buffers               10629120 bytes

This will show fixed and variable size SGA. Fixed size SGA, as I said is not in our control. However we can verify the size of variable SGA and other memory values shown above.

Database Buffers          163840000 bytes

SQL> show parameters db_block_buffer

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_buffers                     integer     20000

This value is in terms of blocks. we can find the size of a block using DB_BLOCK_SIZE parameter

SQL> show parameters db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     8192

So Database Buffers = db_block_buffers X db_block_size = 20000 X 8192 = 163840000 bytes

Also Variable size = “Shared Pool Size” + “Large Pool Size” + “Java Pool size” (some times defined)

SQL> SELECT pool, sum(bytes) from v$sgastat group by pool;

———– ———-
java pool     50331648
shared pool  352321536
Variable size = 352321536 + 50331648 = 402653184 bytes

Program Global Area

PGA contains information about bind variables, sort areas, and other aspect of cursor handling. This is not a shared area and every user has its own PGA. But why PGA is required for every user? The reason being that even though the parse information for SQL or PLSQL may be available in library cache of shared pool, the value upon which the user want to execute the select or update statement cannot be shared. These values are stored in PGA. This is also called Private Global Area.

Going still deeper into the memory structure…

Database buffer cache is again divided into 3 different types of cache.

  1. Default Cache
  2. Keep Cache
  3. Recycle Cache

If we define the cache size using DB_CACHE_SIZE (or DB_BLOCK_BUFFER and specify the block size) then this will be default cache. The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data.

You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks.

  • The KEEP buffer pool retains the schema object’s data blocks in memory. This is defined using the INIT.ORA parameter DB_KEEP_CACHE_SIZE
  • The RECYCLE buffer pool eliminates data blocks from memory as soon as they are no longer needed. This is defined using the INIT.ORA parameter DB_RECYCLE_CACHE_SIZE

You can also define multiple DB block sizes using following parameters. Example if you have defined standard default block size of 4K, then following parameters can be used to define a size of 2K, 8K, 16K and 32K.


Note that you can define the Keep and Recycle cache only on standard block size and buffer cache size is the sum of sizes of each of these pools.

Shared Pool Reserved Size

Shared Pool, as we have seen previously contains the parsed SQL statements and execution plans. With continuous use of database, after a period of time the shared pool will get fragmented. New parsed SQL and execution plans comes and old one gets aged out and hence overwritten. This will also lead to larger packages being aged out with new entries going into shared pool. Hence access to such larger packages will take time to parse and create execution plan. This might cause performance issues.

To avoid such situation, you can define a parameter SHARED_POOL_RESERVED_SIZE. This will reserve some additional space other then shared_pool_size. If an object (either parsed SQL statement or execution plan) is stored in reserved shared pool area then it will not age out.

For large allocations, the order in which Oracle attempts to allocate space in the shared pool is the following:

  1. From the unreserved part of the shared pool.
  2. If there is not enough space in the unreserved part of the shared pool, and if the allocation is large, then Oracle checks whether the reserved pool has enough space.
  3. If there is not enough space in the unreserved and reserved parts of the shared pool, then Oracle attempts to free enough memory for the allocation. It then retries the unreserved and reserved parts of the shared pool.

Process Architecture

Oracle has several process running in the background for proper functioning of database. Following are the main categories of process.

  1. Server Process
  2. Background Process

Server Process – to handle the requests of user processes connected to the instance. Server processes (or the server portion of combined user/server processes) created on behalf of each user’s application can perform one or more of the following:

  • Parse and execute SQL statements issued through the application
  • Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA
  • Return results in such a way that the application can process the information

Background Process – An Oracle instance can have many background processes; not all are always present. The background processes in an Oracle instance include the following:

  • Database Writer (DBW0 or DBWn)
  • Log Writer (LGWR)
  • Checkpoint (CKPT)
  • System Monitor (SMON)
  • Process Monitor (PMON)
  • Archiver (ARCn)
  • Recoverer (RECO)
  • Lock Manager Server (LMS) – Real Application Clusters only
  • Queue Monitor (QMNn)
  • Dispatcher (Dnnn)
  • Server (Snnn)

On many operating systems, background processes are created automatically when an instance is started.

Database writer (DBWn) – The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.

Log Writer (LGWR) – The log writer process (LGWR) is responsible for redo log buffer management–writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.

Checkpoint (CKPT) – When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

System Monitor (SMON)The system monitor process (SMON) performs crash recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary-managed tablespaces. If any dead transactions were skipped during crash and instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON wakes up regularly to check whether it is needed.

Process Monitor (PMON)

The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.

PMON periodically checks the status of dispatcher and server processes, and restarts any that have died (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener.

Archiver Process (ARCn)

The archiver process (ARCn) copies online redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.

An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The ALERT file keeps a record of when LGWR starts a new ARCn process.

Recoverer (RECO) – The recoverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures involving distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database’s pending transaction table any rows that correspond to the resolved in-doubt transactions.

The RECO process is present only if the instance permits distributed transactions and if the DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this initialization parameter is zero, RECO is not created during instance startup.

Lock Manager Server (LMS) – In Oracle9i Real Application Clusters, a Lock Manager Server process (LMS) provides inter-instance resource management.

Queue Monitor (QMNn) – The queue monitor process is an optional background process for Oracle Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes. These processes, like the Jnnn processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.

The above once explained are the mail background processes. Please refer to the Oracle documentation for detailed Oracle 9i Architecture.

Hope this helps !!


Oracle9i Database Online Documentation (Release 9.0.1)

Tom Kyte

Renaming a tablespace – Oracle database 9i and 10g

Oracle 9i:


Tablespace created.

For Renaming the tablespace, we don’t have a direct command in 9i. For that we need to follow the below steps.

1) Export all of the objects from the tablespace

[orsbox@ocvmrh2124 9.2.0]$ exp system/manager FILE=test1.dmp LOG=test1.log TABLESPACES=test1

Export: Release – Production on Thu Jul 12 01:02:37 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release – Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export selected tablespaces …
For tablespace TEST1 …
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.

2) Drop the tablespace including contents

SQL> drop tablespace test1 including contents;

Tablespace dropped.

3) Recreate the tablespace


Tablespace created.

4) Import the objects

[orsbox@ocvmrh2124 9.2.0]$ imp system/manager FILE=test1.dmp LOG=test1.log FULL=Y TABLESPACES=test2

Import: Release – Production on Thu Jul 12 01:27:47 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release – Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SYSTEM’s objects into SYSTEM
Import terminated successfully without warnings.

[orsbox@ocvmrh2124 9.2.0]$

Oracle Database 10g:

In Oracle database 10g we can easily rename a tablespace. Renaming of tablespace has been simplified to an extent of a single ALTER TABLESPACE command. Lets see how renaming of tablespace can be done in 10g.

SQL> CREATE TABLESPACE test1 DATAFILE ‘/slot/ems1177/oracle/db/apps_st/data/test1.dbf’ size 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> alter tablespace test1 rename to test2;

Tablespace altered.