Detailed description of ORA-01555 error Simulation

The following is a description of the ORA-01555 from the online documentation Database Error Messages

ORA-01555: snapshot too old: rollback segment number string with name "string"

Too small

Cause: rollback records needed by a reader for consistent read are overwritten

Other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

ORA-01555 error, usually in 2 cases:

1) SQL statement execution time is too long, the UNDO tablespace is too small, or the transaction volume is too large, or too frequently submitted, resulting in consistent read during SQL Execution, the pre-Modified Image (UNDO data) after SQL Execution has been overwritten in the UNDO tablespace, so consistent read block CR blocks cannot be constructed). This is the most common case.

2) during SQL statement execution, the transaction commit time and SQL Execution start time of the accessed block cannot be determined when the delayed block is cleared. This is rare.

Solution to 1st cases:

1) Increase the UNDO tablespace size

2) added the undo_retention time. The default value is 15 minutes.

3) Optimize the SQL statements with errors to reduce the query time. The preferred method is

4) Avoid frequent submission

Through the example below, let's take a look at how ORA-01555 is produced

1. Determine the undo information of the current system.

SQL> show parameter undo

NAME TYPE VALUE

--------------------------------------------------------------------

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

SQL> col file_name for a32;

SQL>/

FILE_NAME BYTES/1024/1024 TABLESPACE_NAME

------------------------------------------------------------------

+ DATA/orcl/datafile/undotbs1.dbf 140 UNDOTBS1

2. Create a New 1 m undo tablespace and switch to this undo tablespace.

SQL> create undo tablespace undotbs2 datafile '+ DATA/orcl/datafile/undotbs2.dbf

Size 1 m;

Tablespace created.

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL> show parameter undo;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS2

SQL>

3. create a test table. When we insert data into the table, the following undotbs2 extension error is reported. We know that the insert statement will write a rollback segment, the insert data is large, leading to rapid growth of undo. When it reaches 1 MB, oracle will first check whether there are undo blocks that can be overwritten, just because they are data blocks without commit, so at this time you will try to extend the rollback segment, the previous creation did not specify automatic expansion, so it produces an error for the ORA-30036

SQL> create table test as select object_id, object_name from dba_objects;

Table created.

SQL> insert into test select * FROM TEST;

Insert into test select * FROM TEST

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'undotbs2'

4. Clear the table, create a new table, and insert two rows of data.

SQL> create table test (id int, name varchar2 (10 ));

Table created.

SQL> insert into test values (1, 'A ');

1 row created.

SQL> insert into test values (2, 'B ');

1 row created.

SQL> commit;

Commit complete.

5. The cursor variable is used to query the table. Here, the query result set of the table is assigned to the variable I of the refcuersor type. print I is used to output the value of the variable. Assume that the time is t1.

SQL> variable I refcursor;

SQL> exec open: I for select * from test;

PL/SQL procedure successfully completed.

6. Open another session, modify the table, and submit the table. Here, the table is modified and submitted through an endless loop.

The purpose of this operation is to overwrite the undo Block Based on the undo features.

SQL> select * from test;

ID NAME

--------------------

1

2 B

SQL> declare

2 I number: = 1;

3 begin

4 loop

5 update test set id = I where name = 'B ';

6 I: = I + 1;

7 if mod (I, 100) = 0 then

8 commit;

9 end if;

10 end loop;

11 end;

12/

7. Wait about 10 seconds to output another session cursor variable I, this time more famous ORA-01555 error appears.

SQL> print I

ERROR:

ORA-01555: snapshot too old: rollback segment number 13 with name

"_ SYSSMU13_3721975596 $" too small

No rows selected

The cause of this error is that the SQL query time is too long. We use cursor variables to create a long query. The error message "undo segment cannot be expanded" is not displayed. Why? Based on the Result Analysis in the previous two examples, we can introduce that the data block after undo segment submission can be overwritten, here, although the undo data volume is only 1 MB, the undo data volume generated by the update operation reaches 1 MB. As this cycle produces a large number of undo data, it cannot be expanded when it reaches 1 MB, the undo block after commit is overwritten, so the undo tablespace is too small, which is also the Cause of this error. According to Cause: rollback records needed by a reader for consistent read are overwritten by other writers. When a user sends an I output request, the cr block is constructed in the buffer cache and then returned to the user, the cr block is constructed from the undo block before modification. Here the undo block has been overwritten countless times, so the error of ORA-01555 is reported.

8. The previous insert statement cannot intuitively analyze whether the data before commit can be overwritten, And the commit in the loop is removed. First guess, what kind of error will be reported here, if the above analysis is correct, there is no doubt it will report a ORA-30036 error, to verify

SQL> declare

2 I number: = 1;

3 begin

4 loop

5 update test set id = I where name = 'B ';

6 if mod (I, 100) = 0 then

7 I: = I + 1;

8 end if;

9 end loop;

10 end;

11/

Declare

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'undotbs2'

ORA-06512: at line 5

The online document provides the Solution Action: If in Automatic Undo Management mode, increase undo_retention setting. otherwise, use larger rollback segments, so in the previous experiment simulation, you only need to expand the undo tablespace or enable automatic growth. In this real environment, it may be that the SQL statement query time is too long, to solve the problem, you must optimize the SQL.

The default value of undo_retention is 900 s, which means that 900 s is retained in the undo segment after commit, but it is not guaranteed that it will not be overwritten within s, from the previous experiment, we can see that the data has been overwritten within S. To ensure that undo data is not overwritten within S, we need to set the retention Value of the undo segment to guarantee.

SQL> select tablespace_name, RETENTION from dba_tablespaces where tablespace_n

E = 'dotbs2 ';

TABLESPACE_NAME RETENTION

-----------------------------------------

UNDOTBS2 NOGUARANTEE

1. Run the following command to change it to guarantee.

SQL> alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SQL> select tablespace_name, RETENTION from dba_tablespaces where tablespace_n

E = 'dotbs2 ';

TABLESPACE_NAME RETENTION

-----------------------------------------

UNDOTBS2 GUARANTEE

2. If we ensure 900 s, we are executing the previous statement, you can imagine it will report ORA-30036, or verify it

SQL> declare

2 I number: = 1;

3 begin

4 loop

5 update test set id = I where name = 'B ';

6 I: = I + 1;

7 if mod (I, 100) = 0 then

8 commit;

9 end if;

10 end loop;

11 end;

12/

Declare

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'undotbs2'

ORA-06512: at line 5

This article is from the "Step by Step" blog, please be sure to keep this source http://5073392.blog.51cto.com/5063392/1294764