How to manage undo data?

Undo is copy of original, premodified data. Its captured for every transaction that changes data. Capturing undo data enables us to ROLLBACK uncommitted data.Undo supports  read consistent and flashback queries. Undo can rewind transactions and tables.Read consistent queries provide results that are consistent with data as of time query started.

For read consistent query to succeed, original information must still exists as undo information.If original data is not available then we receive snapshot too old error.

Reasons behind ORA-01555 Snapshot too old error:

This error occurs due to oracle read consistency mechanism.
Suppose I executed SELECT statement which is taking more than 15 mins to fetch complete rows in one session and performed DML’s on same table which SELECT is fetching data simultaneously with sequential COMMITS in another session.
Now what will happen is SELECT statement will consider data as of timestamp from where it begins execution.
But in another session data has been modified as COMMITS were executed.
Here you will get ORA-01555.

How to configure undo retention?                                                                                                   Undo retention parameter specifies how long a committed undo information to be retained.                                                                                                        

The only time you set this parameter is when 

 1.Undo tablespace has AUTOEXTEND option enabled.                                                                     2.You want to set UNDO retention for LOBS.                                                                                     3.You want guarantee retention.

undo parameters

Here undo retention is 900s i.e 15 mins.We can increase the retention to hold data in undo tablespace more time with guarantee.

UNDO TABLESPACE tablespace                                                                                              [DATAFILE file_specification[,file_specification}…]]                              [extent_management_clause]                                                                      [tablespace_retention_clause]

undo parameters2

So default behaviour is to overwrite the information of committed transactions that has not expired rather than allowing an active transaction  to fail because of lack of undo space.

Changing the undo tablespaces to fixed size helps in flashback operations and limits unnecessary tablespace growth. We can query data at past time interval by following command.

flashback_query

Author Profile

Tejas
Tejas
Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j
Summary
Oracle Performance tuning -Effective tablespace management considerations
Article Name
Oracle Performance tuning -Effective tablespace management considerations
Description
How to handle ORA-0111 Snapshot too old error? Ways to execute flashback queries
Author

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.