Skip to Main Content
  • Questions
  • "snapshot too old: rollback segment number %s with name \"%s\" too small"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kenny.

Asked: November 13, 2020 - 7:55 am UTC

Answered by: Connor McDonald - Last updated: November 16, 2020 - 3:58 am UTC

Category: Database Development - Version: 12.1.0.2.0

Viewed 100+ times

You Asked

Some general info about the setup:
- We have one DB Server that is used for all reporting. That server has all the DB links to ADGs from the different applications.


In this particular case I have a query that consists of 2 queries that are combined in a UNION ALL
Query 1 executes on remote a, Query 2 on remote b;
Executed separately they work just fine, combined in one union all it gives me the error:
"snapshot too old: rollback segment number %s with name \"%s\" too small"

As I look up the error, it always comes back to, try to run at different time when no DML is being executed etc, but that does not stroke with my case.
I can perfectly execute them separately in parrallel with the unioned one and get results while the union all instantly fails. the word instantly is also important here, it's not the case that the query runs for hours;
Qry 1 = 120s; Qry 2 = 90s;
Qry union = <1s error;

Result is reproducable every minute of the day.

and we said...

There's a few known bugs in this area

Known issue of ORA-01555 in Active Dataguard (ADG) (Doc ID 1951318.1)
ORA-1555 from query on RAC ADG Physical Standby Database (Doc ID 19614585.8)
Known issues of Wrong Results in Active Data Guard(ADG) (Doc ID 2490469.1)

so I think this is going to be one you'll need to take up with Support.

Some ideas for a workaround (which I know is not ideal)

1) Try materialize the data in the query

Current query:

select [query1]
union all
select [query2]

Rewrite as

with local_data as ( select /*+ materialize */ [query1] )
select ... from local_data
union all
select [query2]


2) If that doesn't work, could you wrap this logic in a pipeline function, eg

SQL> select empno, ename from scott.emp
  2  union all
  3  select deptno, dname from scott.dept;

     EMPNO ENAME
---------- --------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

18 rows selected.

SQL>
SQL>
SQL> create global temporary table local_copy
  2  ( empno number, ename varchar2(30))
  3  on commit preserve rows
  4  /

Table created.

SQL>
SQL> create or replace
  2  type each_row as object ( empno number, ename varchar2(30))
  3  /

Type created.

SQL>
SQL> create or replace
  2  type row_list as table of each_row
  3  /

Type created.

SQL>
SQL> create or replace
  2  procedure get_the_remote_data is
  3    pragma autonomous_transaction;
  4  begin
  5    delete local_copy;
  6    insert into local_copy
  7    select empno, ename from scott.emp;
  8    commit;
  9  end;
 10  /

Procedure created.

SQL>
SQL>
SQL> create or replace
  2  function workaround_issue return row_list pipelined is
  3
  4  begin
  5    get_the_remote_data;
  6
  7    for i in
  8     ( select empno, ename from local_copy
  9       union all
 10       select deptno, dname from scott.dept
 11     )
 12   loop
 13     pipe row ( each_row(i.empno, i.ename));
 14   end loop;
 15   return;
 16  end;
 17  /

Function created.

SQL>
SQL> select * from workaround_issue();

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

18 rows selected.


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database