Shulou(Shulou.com)06/01 Report--

How to use undo_retention parameters and UNDO tablespace GUARANTEE function to avoid ORA-01555 errors, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, hope you can get something.

ORA-01555 is the famous snapshot too old (Snapshot too Old) problem, and this error was once the thorniest problem for Oracle in the early versions of Oracle. Every developer or DBA doesn't want to see such an error.

The most common cause of ORA-01555 errors is when a query needs to construct a consistent read using a previous image in a rollback segment that has been overwritten. So is there any way to ensure that the data retained in the UNDO tablespace will not be overwritten within the time we set? The answer is yes, with the upgrade and functionality of the Oracle version, the UNDO tablespace provides GUARANTEE functionality in the Oracle 10g version. This feature ensures that our UNDO data will not be easily overwritten. Try this feature.

1. Create UNDO tablespace UNDOTBS_GUARANTEE

Note that we have not set it to auto-extend here.

Sys@ora10g > create undo tablespace UNDOTBS_GUARANTEE datafile'/ oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf' size 5m

Tablespace created.

two。 Enable the GUARANTEE feature of UNDO tablespaces

Sys@ora10g > alter tablespace UNDOTBS_GUARANTEE retention guarantee

Tablespace altered.

3. Adjust parameters related to UNDO tablespaces

1) query the information related to UNDO in the current database

Sys@ora10g > show parameter undo

NAME TYPE VALUE

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

At this point, the default UNDO table space of the system is "UNDOTBS1". We adjust it to the newly created UNDOTBS_GUARANTEE table space.

The default retention time for UNDO is 15 minutes (900 seconds), which we adjusted to 1.5 hours (5400 seconds).

Sys@ora10g > alter system set undo_retention=5400

System altered.

Sys@ora10g > alter system set undo_tablespace=UNDOTBS_GUARANTEE

System altered.

2) adjusted UNDO related parameters

Sys@ora10g > show parameter undo

NAME TYPE VALUE

Undo_management string AUTO

Undo_retention integer 5400

Undo_tablespace string UNDOTBS_GUARANTEE

4. Test the effect of using UNDO table space under GUARANTEE condition

Sec@ora10g > update t set object_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum update t set object_name =' secjssecalskjdf;alksjdfaslkdjf;alskdjfa;sldooler' where rownum update t set object_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum update t set object_name =' asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum update t set object_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum update t set object_name =' asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.