Undo Tablespace

[rakson73]

Hi there ,
I have Oracle 9i database. In its init.ora file following parameters are defined

UNDO_MANAGEMENT = AUTO
UNDO_RETENTION = 10800
UNDO_TABLESPACE = UNDOTBS1

The size of my undo tablespace has grown to 7GB.
I have few queries with undo tablespace.

1. How has undo tablespace grown ?

2. When I have defined UNDO_RETENTION as 10800 sec, should not then the space occupied in undo tablespace be relesed after 10800 sec ?

3. How do I decrease the size of my undo tablespace? Will it have effect on my data too ?


RKS

 

[Scunningham99]

## IF WE WISH TO MAKE AN UNDO TABLESPACE SMALLER, THE ONLY WAY WE CAN DO THIS IS BY CREATING ANOTHER
UNDO TABLESPACE ALLONGSIDE THE EXISTING ONE WHICH IS SMALLER! THEN DROP THE ORIGINAL. FOLLOW STEPS BELOW:-

## TO CREATE UNDO TABLESPACE:-

sql> create undo tablespace UNDOTBS02
datafile 'C:\NTDB1\ORADATA\NTDB1\UNDOTBS02.DBF' size 30M autoextend off
extent management local


## WE CAN THEN DYNAMICALLY ALTER THE UNDO_TABLESPACE PARAMETER

sql> alter system set undo_tablespace = UNDOTBS02;



## WE CAN NOW DROP THE ORIGINAL UNDO TABLESPACE

sql> drop tablespace undotbs including contents and datafiles;

-----------
UNDO_RETENTION specifies (in seconds) the amount of committed undo information to retain in the database. You can use UNDO_RETENTION to satisfy queries that require old undo information to rollback changes to produce older images of data blocks. You can set the value at instance startup.

The UNDO_RETENTION parameter works best if the current undo tablespace has enough space for the active transactions. If an active transaction needs undo space and the undo tablespace does not have any free space, then the system will start reusing undo space that would have been retained. This may cause long queries to fail. Be sure to allocate enough space in the undo tablespace to satisfy the space requirement for the current setting of this parameter.



Sy UK

 

[Scunningham99]

## IF WE WISH TO MAKE AN UNDO TABLESPACE SMALLER, THE ONLY WAY WE CAN DO THIS IS BY CREATING ANOTHER
UNDO TABLESPACE ALLONGSIDE THE EXISTING ONE WHICH IS SMALLER! THEN DROP THE ORIGINAL. FOLLOW STEPS BELOW:-

## TO CREATE UNDO TABLESPACE:-

sql> create undo tablespace UNDOTBS02
datafile 'C:\NTDB1\ORADATA\NTDB1\UNDOTBS02.DBF' size 30M autoextend off
extent management local


## WE CAN THEN DYNAMICALLY ALTER THE UNDO_TABLESPACE PARAMETER

sql> alter system set undo_tablespace = UNDOTBS02;



## WE CAN NOW DROP THE ORIGINAL UNDO TABLESPACE

sql> drop tablespace undotbs including contents and datafiles;


Sy UK

 

[Scunningham99]

further exlanation:-

Undo Retention Control
Long-running queries sometimes fail because undo information required for consistent read operations is no longer available. This happens when committed undo blocks are overwritten by active transactions.

Automatic undo management provides a way to explicitly control when undo space can be reused; that is, how long undo information is retained. A database administrator can specify a retention period by using the parameter UNDO_RETENTION. For example, if UNDO_RETENTION is set to 30 minutes, then all committed undo information in the system is retained for at least 30 minutes. This ensures that all queries running for 30 minutes or less, under usual circumstances, do not encounter the OER error, "snapshot too old."

You can either set UNDO_RETENTION at startup or change it dynamically with the ALTER SYSTEM statement. The following example sets retention to 20 minutes:

ALTER SYSTEM SET UNDO_RETENTION = 1200;


If you do not set the UNDO_RETENTION parameter, then Oracle uses a small default value that should be adequate for most OLTP systems, where queries are not usually not very long.

In general, it is a good idea not to set retention to a value very close to what the undo tablespace can support, because that may result in excessive movement of space between undo segments. A 20% buffer of undo space is recommended.


HTH



Sy UK

 

[rakson73]

Hi UK

Again, what is the purpose of UNDO_RETENTION ? I can understand that it retains the information for that period, generally for the purpose of flash-back queries.

But suppose I have specified UNDO_RETENTION = 10800 , i.e 3 hrs.
I issues a last DML at 05.00 pm . Now when I see at 09.00 pm, should no all space occupied by undo tablespace be release ? B'cos it has to hold the information max for 3 hrs.

 

[alexhu]

answer to Q1 (if you haven't found it already) you have autoextend 'on' on the tablespace. take it off.

Alex