Oracle ошибка ora 01555

ORA-01555 — Причина ошибки — недостаточный размер сегмента отката.

Лечение:

Нужно обеспечить сохранность информации в UNDO на всё время пока выполняется запрос. Для этого нужно иметь достаточный размер параметра UNDO_RETENTION и достаточный размер табличного пространства UNDO.

Т.е. проще говоря, для того чтобы избежать возникновения ORA-01555 нужно увеличивать размер табличного пространства UNDO и параметр UNDO_RETENTION до тех пор пока операция не пройдет без ошибки.

Предварительная подготовка

1) Убедиться что UNDO управляется автоматически. Т.е. параметр БД UNDO_MANAGEMENT = AUTO.

Если не так, включить автоматическое управление (требуется перезапуск БД):

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

После чего перезапустить БД.

2) Настройка табличного пространства UNDO

— Определить какого размера UNDO сейчас

SELECT SUM(a.bytes)/1024/1024 as "UNDO_SIZE_IN_MB"
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND c.tablespace_name = 'UNDO11' -- для RAC
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

— Табличное пространство UNDO состоит из одного файла???

— На диске где лежит файл табличного пространства UNDO еще есть свободное место??? Сколько его???

Здесь главное понять что файлы табличного пространства имеют достаточный размер (лучше сделать их авторасширяемыми) и их достаточное количество и на диске есть месть для авторасширения файлов.

3) Нужно определить наибольшее время выполнения SQL-запроса (т.е. время потраченное на выполнение самого долгого запроса).  Для этого есть несколько способов. Если ни один из способов не выявил большого времени выполнения запроса, тогда придется экспериментальным путем устанавливать этот параметр. Можно сразу установить заведомо большое значение, но при этом нужно помнить что чем больше UNDO_RETENTION тем до большего размера может вырасти табличное пространство UNDO и это может закончиться переполнением диска, на котором находятся файлы UNDO.

   3.1) Посмотрите alert.log на предмет наличия ошибки ORA-01555 в то время когда выполнялся экспорт. В сообщение об ошибке может быть указанно время в сек. Выполнения операции. Параметр UNDO_RETENTION нужно установить не менее чем это время, а лучше раза в два больше.

ORA-01555 caused by SQL statement below (SQL ID: 738wa64wpd5s2, Query Duration=224611 sec, SCN: 0x0a0e.44620301):

   3.2) Если в alert.log ничего нет, то можно попробовать определить оптимальный начальный UNDO_RETENTION. Выполнять под SYS.

— Покажет колл. секунд выполнения самого долгого запроса за последние 7 дней

SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT;

Параметр UNDO_RETENTION нужно установить большим чем полученное значение (можно сделать в два раза больше, чтобы иметь запас). После выполнения запроса, если он не выполняется регулярно, лучше вернуть UNDO_RETENTION исходное значение, чтобы табличное пространство UNDO не разрасталось.

4) Чтобы уменьшить вероятность возникновения ORA-01555 нужно чтобы с БД во время экспорта вообще никто не работал (потому что другие сессии могут также увеличивать UNDO). Идеально если экспорт будет выполняться вообще один. Нужно учесть что с БД могут работать не только пользователи, но и службы и пакетные задания (batcmd). Т.е. на время экспорта лучше остановить все службы, все задания и т.п.

Any idea about
ORA-1555: snapshot too old: rollback segment number
I am getting this error and nothing seems to be wrong. Please state under what conditions in may occur and how it can be avoided?

asked Nov 6, 2009 at 9:58

Sachin Chourasiya's user avatar

Sachin ChourasiyaSachin Chourasiya

20.7k32 gold badges86 silver badges98 bronze badges

4

Frequent commits can be the cause of ORA-1555.
It’s all about read consistency. The time you start a query oracle records a before image. So the result of your query is not altered by DML that takes place in the mean time (your big transaction). The before image uses the rollback segments to get the values of data that is changed after the before image is taken.
By committing in your big transaction you tell oracle the rollback data of that transaction can be overwritten.
If your query need data from the rollback segments that is overwritten you get this error. The less you commit the less chance you have that the rollback data you need is overwritten.

One common cause of ORA-1555 is a procedure that does this all in itself : a cursor on a table, loop through the records, and updates/delete the same table and commits every x records.

As guigui told : let the rollback segments grow to contain your whole transaction

answered Nov 6, 2009 at 15:22

Rob van Laarhoven's user avatar

I suggest you read Tom’s answer :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1441804355350

«The ORA-1555 happens when people try to save space typically. They’ll have small
rollback segments that could grow if they needed (and will shrink using OPTIMAL). So,
they’ll start with say 10 or so 1meg rollback segments. These rollback segments COULD
grow to 100meg each if we let them (in this example) however, they will NEVER grow unless
you get a big transaction.
«

answered Nov 6, 2009 at 10:03

guigui42's user avatar

2

Typically this occurs when code commits inside a cursor.

eg.

for x in (select ... from ...)
loop
   do something
   commit;
end loop;

See the AskTom link form guigui42 though for other examples.

answered Nov 6, 2009 at 11:45

David Aldridge's user avatar

David AldridgeDavid Aldridge

51.3k8 gold badges68 silver badges95 bronze badges

3

Let’s see the error ORA-01555 UNDO_RETENTION first:

Description

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 by other writers

Action

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

Before we drill down to a deeper topic, we need to clarify some concepts:

  1. Undo space reclaimation
  2. SMON will keep all undo as long as possible. It will do nothing about reclaimable candidates until the space is in pressure. When time comes, it will choose from the oldest to the nearest in order to reclaim.

    In fact, lazy reclaimation is very popular algorithm on swiping unused but occupied resources. For example, we won’t clean the snow on the roof every time it falls until it looks too heavy.

  3. Uncommitted active undo
  4. It is never overwritten or cleaned even though the undo space is full. Once undo space is full with uncommitted undo, the later DML will fail.

  5. Committed expired undo
  6. In terms of UNDO_RETENTION, committed expired undo is an undo data which ages over the limit defined by UNDO_RETENTION. It’s a no-doubt candidate to be overwritten or cleaned.

  7. Committed unexpired undo
  8. It depends on whether guaranteed is enforced or not to determine the behavior.

    • RETENTION GUARANTEE
    • The data is not a reclaimable candidate until expired, but you must declare GUARANTEE explicitly.

    • RETENTION NOGUARANTEE
    • The data is a reclaimable candidate although it’s not expired. This is the default behavior of undo management without declaration.

  9. Rolled back undo
  10. It’s an expired undo which will become a reclaimable candidate once the rolling back is complete.

Raising the value of UNDO_RETENTION seems a good idea to solve the problem, but you may be disappointed in some conditions. You may check my post for more explanations: UNDO_RETENTION, How and Why

In this post, I will talk about ORA-01555 UNDO_RETENTION from two different perspectives to find the ways to reduce such error. You may judge the situation before actually doing it.

Solutions

The solutions depend on what condition that your database is able to provide.

When Resizing UNDO is Possible

UNDO_RETENTION is just an honored value to your queries in the default condition which is RETENTION NOGUARANTEE. Even more, undo space could be full before queries reach the time limit of UNDO_RETENTION and you will get ORA-01555 very early. Therefore, raising UNDO_RETENTION is useless, you need to enlarge the space if you can.

  1. Resize current datafile.
  2. SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' resize 800m;

  3. Autoextend current datafile.
  4. SQL> alter database datafile '/u01/oracle/rbdb1/undotbs01.dbf' autoextend on;

  5. Add a new datafile.
  6. SQL> alter tablespace undotbs add datafile '/u01/oracle/rbdb1/undotbs02.dbf' autoextend on maxsize unlimited;

    Further reading: How Big a Data File Can Be?

Now, the undo data will be kept as long as the space is enough for new transactions.

When Resizing UNDO is NOT Possible

If your UNDO tablespace cannot be resized or auto extended in this moment, then what you can do is very limited to none. My strategy is to compromise new transactions instead of queries so as to avoid ORA-01555. Which means, we raise and guarantee UNDO_RETENTION can be reached, no matter what.

  1. Raise UNDO_RETENTION whenever AUTOEXTEND is enabled.
  2. SQL> alter system set undo_retention = 14400 scope=both sid='*';

    UNDO_RETENTION is kind a soft limit which is no guarantee. If the space is no longer extendable for new undo, the oldest committed undo will be sacrificed. So we have to make it GUARANTEE.

  3. Enforce GUARANTEE on UNDO_RETENTION.
  4. SQL> alter tablespace undotbs retention guarantee;

    To reverse the setting, you can do this:

    SQL> alter tablespace undotbs retention noguarantee;

    The database will never touch unexpired undo with RETENTION GUARANTEE even if new DML transactions fail.

    Therefore, a better practice is that you should ensure the undo space is enough to extend before enabling RETENTION GUARANTEE, otherwise, new DML transactions will fail due to a full undo.

More concepts can be found at Oracle website: Managing Undo

Just remember, keep your undo data in the UNDO tablespace as long as possible is the key to reduce ORA-01555.

«Can some one tell me what I am doing
wrong»

Where to start?

ORA-01555

This occurs in long-running queries. Oracle’s policy of read consistency guarantees that the last record in the result set is consistency with the first record of the result set. In other words, our query will not return changes made in some other session which were committed after we issued our query. Oracle does this by substituting records from the UNDO tablespace for any changed records. When it cannot do this it hurls the SNAPSHOT TOO OLD exception. This means Oracle no longer has the old versions of the records it needs to provide a read-consistent view.

A common reason why Oracle no longer has the data is because our long-running query is a PL/SQL cursor loop, which issues COMMIT statements. As you should know, COMMIT signifies the end of a transaction, and that releases any locks Oracle has been keeping for our session. This obviously includes our sessions interest in the UNDO tablespace; Oracle is then free to overwrite the UNDO blocks which contain the data need for read consistency.

In your case the COMMIT statements are the implicit ones which bracket any DDL statement — including ANALYZE. This might not matter but it seems somebody is updating the SCENARIOS table while your program runs, a likely ocurance for something which takes several days.

Use of ANALYZE

This is bad for several reasons. The first is that it has been deprecated for quite a while: you’re on 10g, you should be using DBMS_STATS to gather statistics. But wait, there’s more.

Gathering statistics is not something which should be done too often. In most systems the statistics acheive a plateau of stability, in which they are accurate enough even when they are several months old. So, frequent gathering of statistics is at best a waste of cycle. It can be much worse: there is the risk of fresh statistics spawning a less efficient plan than the ciurrent one. So actually statistics gathering should be done in a controlled fashion. One of the advantages of DBMS_STATS is that we can configure it to monitor the rate of changes applied to our tables, and only re-gather statistics when they reach a certain staleness. Find out more.

Of course you’re only using ANALYZE to get the up-to-date space usage for the indexes, which bings me to my third point:

Insanity

You are select every row of all the tables you’re interest in and totalling the actual size of all their columns, with — if I have understaood correctly — a separate query for each column. This is insane.

Oracle provides views which show teh amount of space used by a given table. USER_SEGMENTS should be sufficient, although USER_EXTENTS is also available. The SEGMENT_NAME is the index or table name. Summing the BYTES column will give you an exact size of the footprint of each table.

Of course, some of those allocated extents will be empty, so you may think those figures will be a slight overestimate. But:

  1. The alloocated extents is actually a more accurate picture of the space usage, because it allows for the space which is held by the table.
  2. Any perceived loss of «accuracy» will be repaid in queries which will run in seconds rather than days.
  3. Following on from that, the queries will return the position now rather than a shifting picture of space usage over the course of three days, so the figures are much more useful.

«but the whole motivation behind
writing this whole PL/SQL script was
to get the ACTUAL not ALLOCATED Space»

Okay, let’s tackle that. The main problem with your script is that it tackles things RBAR; in fact worse than that, RBARBAC. So you issue a matrix of queries, one for each column of each row of the table. SQL is a set-based language, and it wroks a lot better if we treat it as such.

This procedure assembles a dynamic query which assembles a single SELECT to get the total size and number of records for a given table.

create or replace procedure get_table_size 
    ( p_tabn in user_tables.table_name%type
      , p_num_rows out pls_integer
      , p_tot_size out pls_integer )
is
    stmt varchar2(32767) := 'select count(*), sum(';
    n_rows pls_integer;
    n_size pls_integer;
begin
    for r in ( select column_name, data_type, column_id
               from user_tab_columns
               where table_name = p_tabn
               order by column_id)
    loop
        if r.column_id != 1
        then
            stmt := stmt ||'+';
        end if;
        stmt := stmt || 'nvl(';
        if r.data_type in ('CLOB', 'BLOB', 'BFILE') 
        then
            stmt := stmt || ' dbms_lob.getlength('||r.column_name||')';
        else
            stmt := stmt || ' vsize('||r.column_name||')';
        end if;
        stmt := stmt || 'nvl)';
    end loop;
    stmt := stmt || ') from '||p_tabn;
    execute immediate stmt into n_rows, n_size;
    p_num_rows := n_rows;
    p_tot_size := n_size;
end;
/

It doesn’t include the block header overhead (3 bytes per row) but that’s a matter of simple arithmetic.

Here it is in action:

SQL> desc t34
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQ_NUM                                            NUMBER
 UNIQUE_ID                                          NUMBER
 NAME                                               VARCHAR2(20 CHAR)
 LONG_COL                                           CLOB

SQL>
SQL> set timing on
SQL> var n1 number
SQL> var n2 number
SQL> exec get_table_size('T34', p_num_rows=>:n1, p_tot_size=>:n2)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL> print n1

        N1
----------
        11

SQL> print n2

        N2
----------
    135416

SQL>

Small table, perhaps unrealistically fast. Here’s a bigger one, no clobs.

SQL> exec get_table_size('BIG_TABLE', p_num_rows=>:n1, p_tot_size=>:n2)

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.65
SQL> print n1

        N1
----------
   4680640

SQL> print n2

        N2
----------
 189919606

SQL>

The elapsed time is still good, mmm?

Regarding space for indexes, a similar query would work, only driving from USER_IND_COLUMNS to get the appropriate column names. I think that is preferable to re-analyzing the indexes. It won’t work for sizing any TEXT indexes you may have on CLOB or BLOB columns. For those you need to use CTX_REPORT.INDEX_SIZE(), although that produces a report which you would need to parse in oder to get the useful figures (the XML format might be helpful in that regard).

An ORA-01555 error can occur on the Oracle database used by the RSA Identity Governance & Lifecycle product when it is trying to perform a query but does not find the read consistent data it is looking for. This is one of the prominent errors caused by Oracle’s read consistency model.

For more information, please review Oracle Support Note 40689.1 — ORA-01555 «Snapshot too old» — Detailed Explanation.

Login to the Oracle Support portal to access this note and others referred to in this article.

The following statements are taken from Note 40689.1:

ORA-01555 Explanation

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a ‘read consistent’ image. These are:

  • The rollback information itself is overwritten so that Oracle is unable to roll back the (committed) transaction entries to attain a sufficiently old enough version of the block.
  • The transaction slot in the rollback segment’s transaction table (stored in the rollback segment’s header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

The following solutions are from the Oracle Support Note 269814.1 — ORA-01555 Using Automatic Undo Management — Causes and Solutions (login to the Oracle Support portal to access this note).

  1. The UNDO tablespace is too small.
  2. Tune the value of the UNDO_RETENTION parameter.
  3. Enable retention guarantee for the Undo tablespace.
  4. Calculate the size of the UNDO tablespace.

There are also solutions for ORA-01555 errors in specific circumstances, where there are Oracle Support notes available.  Given that the steps are quite detailed, please view these notes on the Oracle Support portal.

  1. Oracle Support Note 1950577.1 — IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds
  2. Oracle Support Note 846079.1 : LOBs and ORA-01555 troubleshooting and Note 452341.1 : ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption 

However, the problem is most likely due to a long-running query, so the Oracle AWR report needs to be generated and examined to identify the long-running query. The ability to generate AWR requires licensing for Oracle Diagnostics Pack; for more details, refer to the article 000037217 — Licensing for Oracle Automatic Workload Repository (AWR) with RSA Identity Governance & Lifecycle.

Once the long-running query has been identified, the solution will be to determine why it is long-running.  In some cases, it may be a known issue with the RSA Identity Governance and Lifecycle setup for the Oracle database, or it may be that large Collections need to be re-scheduled.  If necessary, please log a case so that an RSA Support engineer can assist.

Please note that all the SQL statements in this section need to be run as SYSDBA, not as the AVUSER account. This is because the tables being accessed and the operations being performed need SYSDBA access. If you are unsure, please consult your Oracle DBA or engage an RSA Support Engineer.

1.  The UNDO tablespace is too small

A method for determining the «number of bytes needed to handle a peak undo activity» is detailed in Oracle Support Note 262066.1 — How To Size UNDO Tablespace For Automatic Undo Management (login to the Oracle Support portal to access this note).  However, for your convenience, here is the SQL.
 

For this SQL to return valid results, it needs to be run during peak workload; that is, a time similar to when the ORA-01555 error occurred.

  1. Run the following command and note the output below:

SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
     Bytes 
---------- 
 269519503

The Undo Tablespace would need to be at least the calculated number of bytes.  However, allow 10-20% when re-sizing or adding data files to the Undo Tablespace.

  1. Next, show the sizes and Autoextend setting for the current Data Files used by the Undo Tablespace, along with the output.  Note that the results below do not show a problem.

COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_NAME, BYTES/1024/1024 AS "BYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
FILE_NAME                                                                        BYTES (MB) AUTOEXTENSIBLE 
-------------------------------------------------------------------------------- ---------- -------------- 
/u01/app/oracle/oradata/AVDB/undotbs01.dbf                                              440 YES 
/u01/app/oracle/oradata/AVDB/undotbs02.dbf                                              128 YES 
/u01/app/oracle/oradata/AVDB/undotbs03.dbf                                              128 YES
  1. Add the required space to the Undo Tablespace, as per Oracle Support Note 1951696.1 — IF: How to Resize the Undo Tablespace.  The steps from section 2. Add Space to the Undo Tablespace from Note 1951696.1 have been reproduced here, for your convenience.
    1. To resize the existing undo datafile:

col T_NAME for a23
col FILE_NAME for a65
SELECT tablespace_name T_NAME,file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') ORDER BY file_name;

ALTER DATABASE DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' resize <SIZE>M;

For example,


ALTER DATABASE DATAFILE 'D:ORACLE_DBTESTDBTESTDBUNDOTBS01.DBF' RESIZE 1500M;
  1. Add a new datafile:

ALTER TABLESPACE <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' SIZE 300M;

For example,


ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'D:ORACLE_DBTESTDBTESTDBUNDOTBS02.DBF' SIZE 300M;

2.  Tune the value of the UNDO_RETENTION parameter

The following is from Oracle Support Note 269814.1 — ORA-01555 Using Automatic Undo Management — Causes and Solutions.  It is reproduced here for your convenience.

This is important for systems running long queries. The parameter’s value should at least be equal to the length of the longest-running query on a given database instance. This can be determined by querying V$UNDOSTAT view once the database has been running for a while:


SQL> SELECT MAX(maxquerylen) FROM v$undostat;

The V$UNDOSTAT view holds undo statistics for 10-minute intervals. This view represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance. This view contains the following columns:

Column name

Meaning

BEGIN_TIME

The beginning time for this interval check

END_TIME

The ending time for this interval check

UNDOTSN

The undo tablespace number

UNDOBLKS

The total number undo blocks consumed during the time interval

TXNCOUNT

The total number of transactions during the interval

MAXQUERYLEN

The maximum duration of a query within the interval

MAXCONCURRENCY

The highest number of transactions during the interval

UNXPSTEALCNT

The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests

UNXPBLKRELCNT

The number of unexpired blocks removed from undo segments to be used by other transactions

UNXPBLKREUCNT

The number of unexpired undo blocks reused by transactions

EXPSTEALCNT

The number of attempts when expired extents were stolen from other undo segments to satisfy a space request

EXPBLKRELCNT

The number of expired extents stolen from other undo segments to satisfy a space request

EXPBLKREUCNT

The number of expired undo blocks reused within the same undo segments

SSOLDERRCNT

The number of ORA-1555 errors that occurred during the interval

NOSPACEERRCNT

The number of Out-of-Space errors

  • When the columns UNXPSTEALCNT through EXPBLKREUCNT holds non-zero values, it is an indication of space pressure.
  • If column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
  • If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

To easily determine if the conditions from Note 269814.1 have been met, please use the SQL statements below.

  • Query to determine if UNDO_RETENTION is properly set, where 0 means that no tuning is needed.  

SELECT COUNT(*) as "Tune UNDO_RETENTION" FROM V$UNDOSTAT WHERE SSOLDERRCNT > 0;

If this query returns a non-zero value (count), then it is likely that the UNDO_RETENTION needs to be changed to the maximum value of column v$undostat.maxquerylen (see above).

  • Query to determine if there is Space Pressure, where 0 means no.

SELECT COUNT(*) AS "Space Pressure" FROM V$UNDOSTAT WHERE UNXPSTEALCNT > 0 OR UNXPBLKRELCNT > 0 OR UNXPBLKREUCNT > 0 OR EXPSTEALCNT	> 0 OR EXPBLKRELCNT > 0 OR EXPBLKREUCNT > 0;

If this query returns a non-zero value (count), then space

may

need to be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).

  • Query to determine is there is a serious space problem, where 0 means no.  

SELECT COUNT(*) AS "Serious Space Problem" FROM V$UNDOSTAT WHERE NOSPACEERRCNT > 0;

If this query returns a non-zero value (count), then space should be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).
 

3.  Enable retention guarantee for the Undo tablespace

There are several Oracle Support notes that explain why this is necessary.  See:

  • Oracle Support Note 1100313.1 — Tuned_UndoRetention Can be Less Than Undo_Retention in Init.ora
  • Oracle Support Note 1579779.1 — Automatic Tuning of Undo Retention Common Issues

The explanation is «In the event of any undo space constraints, the system will prioritize DML operations over undo retention. In such situations, the low threshold may not be achieved and tuned_undoretention can go below undo_retention.».

So, if you see V$UNDOSTAT.TUNED_UNDORETENTION being less than the UNDO_RETENTION, then setting RETENTION GUARANTEE is recommended by Oracle.
For example:


SQL> SHOW PARAMETER undo_retention
NAME                       TYPE       VALUE
-------------------------- ---------- ------------------------------
undo_retention             integer    900

SQL> SELECT MIN(TUNED_UNDORETENTION) FROM V$UNDOSTAT;
MIN(TUNED_UNDORETENTION)
------------------------
511

This solution means that the Undo data will never be overwritten, where according to the algorithm the Undo Tablespace will instead be extended.

  1. Determine the Undo Tablespace name.

SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';
  1. Using the tablespace name returned by the above query, enable Retention Guarantee on the Undo Tablespace.

ALTER TABLESPACE <tablespace-name> RETENTION GUARANTEE;

4.  Calculate the size of the UNDO tablespace

The Oracle advice here is to «Use the formula presented in Document 262066.1 to calculate the size of the UNDO tablespace,» however, this formula has already been presented in section 1.  The UNDO tablespace is too small.

    Возможно, вам также будет интересно:

  1. Oracle ошибка oci dll
  2. Oracle sql обработка ошибок
  3. Oracle sql developer ошибка 17002
  4. Oracle jre 7 update 51 64 bit or higher is required for polybase ошибка
  5. Oracle insufficient privileges ошибка

  6. Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии