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

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j