Home Database Mysql Tutorial Oracle ORA-01555(快照过旧)

Oracle ORA-01555(快照过旧)

Jun 07, 2016 pm 04:44 PM

ORA-01555 快照过旧,是数据库中很常见的一个错误,比如当我们的事务需要使用undo来构建CR块的时候,而此时对应的undo已经不存在

一、引言:

[Oracle@]$ oerr ora 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//      overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

    ORA-01555 快照过旧,是数据库中很常见的一个错误,比如当我们的事务需要使用undo来构建CR块的时候,而此时对应的undo已经不存在了,这个时候就会报ORA-01555的错误。

    ORA-01555错误在Oracle 8i及之前的版本最多。从9i开始的undo自动管理,至现在的10g、11g中的undo auto tuning,使得ORA-01555的错误越来越少。但是这个错误,仍然不可避免。

二、出现ORA-01555错误,通常有2中情况:

    (1)、SQL语句执行时间太长,或者undo表空间过小,或者事务量过大,或者过于频繁的提交,,导致执行SQL过程中进行一致性读时,SQL执行后修改的前镜像(既UNDO数据)在UNDO表空间中已经被覆盖,不能构造一致性读块(CR blocks)。这种情况最多。

    (2)、SQL语句执行过程中,访问到的块,在进行延迟块清除时,不能确定该块的事务提交时间与SQL执行开始时间的先后次序。这种情况很少。

三、模拟ORA-01555场景:

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

Table created.

SQL> insert into jack values(1,'a');

1 row created.

SQL> insert into jack values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> show parameter undo

NAME                    TYPE      VALUE
-------------------- ----------- --------------
undo_management          string      AUTO
undo_retention          integer    900
undo_tablespace          string      UNDO2
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/yft/undo01.dbf' size 1m;

Tablespace created.

SQL> alter system set undo_retention=1;

System altered.

SQL> var x refcursor
SQL> begin
  2  open :x for select * from jack;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> begin
  2    for i in 1 .. 20000 loop
  3  update jack set id=id+1;
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print :x
ERROR:
ORA-01555: snapshot too old: rollback segment number 17 with name
"_SYSSMU17_2039231318$" too small

 

no rows selected

四、第1种情况解决的办法:

(1)、增加UNDO表空间大小;

(2)、增加undo_retention时间,默认只有15分钟;

(3)、优化出错的SQL,减少查询的时间,首选方法;

(4)、避免频繁的提交。

Oracle ORA-01555 快照过旧 说明 

ORA-01078 和 LRM-00109 报错解决方法

ORA-01555超长的Query Duration时间 

ORA-00471 处理方法笔记

ORA-00314,redolog 损坏,或丢失处理方法

ORA-00257 归档日志过大导致无法存储的解决办法

本文永久更新链接地址:

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles