Thiruvaiyaru -- Thanks for the question regarding "Rollback Segment ", version 8.1.6

  July 26, 2001 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from Switzerland / Zuerich

COMMITs and ORA-01555 errors   July 26, 2001 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Logo Palanisamy from Santa Clara, USA

Tom,

I understand that ORA-1555 are caused mainly by the commits in a loop. But how is the "loop .. exit 
when .. end loop" different from the "cursor for loop" as far preventing ORA-1555 error. Both of 
them commit after every 1000 records. 

Won't the "loop .. exit when ..commit end loop" also create the same ORA-1555 error? 

LOOP ...................... END LOOP   July 27, 2001 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Bhawna R from India

I understand the basic problem with the cursor for loop of the example, this has to do with 
refering to the same table for LOOPING condition which is getting updated within the LOOP. But, we 
have a job where a cursor is declared for selecting records from table X within DECLARE section. 
And, in the procedure body, there is a LOOP.....END LOOP, where the processing is done. Records are 
fetched from the cursor, some records are inserted in 4-5 tables, andrecords are deleted from 30-35 
tables (including X). This job takes around 1 hour plus to complete, and sometimes it gives 
ORA-1555. We have used SET TRANSACTION to use BIG_ROLLBACK segment. There is COMMIT at the end of 
this (within LOOP), as otherwise it would give rise to UNABLE TO EXTEND ROLLBACK SEGMENT. This is 
actually a purging job and it runs everyday once. Now, we need to add 10 more tables to it, as a 
few more modules have been added. Could you advice us how to handle this situation? The main table 
(cursor table) has some 2 million records. 

New Idea   July 30, 2001 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Pramodu from U.A.E, Middle East

Dear Tom
I was getting this error message, now i can solve my problem

Thanks 

Tricks   July 31, 2001 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Anthony Lai Cheuk Tung from Hong Kong SAR

Thank you , Tom, it is a nice trick that we always ignore . May be, apart from looking into your 
Wrox book, as a DBA or developer, we are required to log down our knowledge and share with others. 
If this forum can be searched by catalogs, it is excellent. 

Work hard , Tom! I have to learn from you and myself as well as from all of Oracle fans.

Anthony
 

  August 2, 2001 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

what about UNDOTBS in oracle 9i   September 29, 2002 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Irfan from Kuwait

Hi Tom, 

I am using UNDO tablespace in oracle 9i with undo_management=Auto; 
How does it control rollback segment? Does system controls the size of rollback segments ?

I tried to insert 95000 records in a table in oracle 9i with default undo_retention. I am still 
getting ora-1555 and ora-6512. By the way I am doing commit in cursor for loop. What should I do to 
resove this error ? should I increase undo_retention value or use commit outside the loop?

Thanks
 

Yes, the entire purpose of AUTO is to have Oracle

o decide how many (we'll create new rbs's in this undo tablespace as we need)
o decide how big (based on your retention period)

If you are getting ora-1555, you set the undo retention TOO small -- it needs to be set for longer 
then your longest running query to avoid the 1555.


(but YES, YES YES you should commit OUTSIDE THE CURSOR FOR LOOP)!!!!!

The "theory" behind committing inside the cursor for loop goes like this "it'll save on the amount 
of rollback segment space I need".  Hopefully you can see how woefully wrong this is.  YOU NEED 
that undo in order for your query to proceed.  Yet, when you commit, you released that undo for 
subsequent reuse -- which since the undo retention period is small -- WE DID.  Hence, when you 
increase the undo retention, you will increase the amount of undo we save, which means all your 
commits in the cursor for loop are doing for you are precisely:

o making you run slower
o generating more resources
o removing data integrity from your database


Thats all those frequent commits are accomplishing!!!! 

followup   September 29, 2002 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Irfan from Kuwait

In your eg you select the rowid from table t and then use that rowid to delete data from table same 
table t.
But in our case :
We select from table x and insert into table y;
eg

DECLARE
CURSOR C1 IS
   SELECT * FROM PGIT_POLICY
   WHERE  POL_APPR_STS = 'A'
   AND    POL_CLASS_CODE = '001'
   AND    POL_SEG_03 = '02'
   AND    TO_CHAR(POL_ISSUE_DT,'YYYY') = '2000';

BEGIN

FOR C1REC IN C1
LOOP
INSERT INTO PGITH_POLICY (POLH_SYS_ID)
SELECT
POL_SYS_ID
FROM PGIT_POLICY 
WHERE POL_SYS_ID = C1REC.POL_SYS_ID;

INSERT INTO PGITH_POL_ADDL_INFO (PAIH_SYS_ID)
SELECT
PAC_SYS_ID
FROM  PGIT_POL_APPL_CURR       
WHERE PAC_POL_SYS_ID   = C1REC.POL_SYS_ID  ;

like this there are another 8 tables. 

COMMIT;
END LOOP;
END;

So here our tables from where we select is different and table where we insert is different. Do u 
still think putting COMMIT in this kind of FOR LOOP is wrong and it will generate ora - 1555. 

yes.  see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:895410916429

for example -- there I query a read only tablespace (read only) and generate the 1555 all by myself 
by committing in the cursor for loop.

Only commit in the cursor for loop if:

a) you want to waste resources (generate more redo, waste cpu cycles)
b) you want to run slower
c) you want to lose data integrity


If any of those bother you -- don't commit in the cursor for loop!

 

OK   June 30, 2004 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Raju from Hyderabad,India

Hi Tom,
If I insert some rows into a table,What will be the Undo 
generated for the inserted rows?Will it be Delete entries for
the inserted rows or previous memory address of the table's
data blocks which existed prior to row inserts.Is there any way to check that?
Please do reply.
Bye!
 

if you insert rows, undo will consist of "delete this rowid".

 

ORA-01555: snapshot too old: rollback segment   November 7, 2007 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Anne from MN,USA

CREATE MATERIALIZED VIEW BPS_MV 
TABLESPACE BPS_DATA
PCTUSED    40
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
USING INDEX TABLESPACE BPS_INDEX
REFRESH COMPLETE   ON DEMAND   WITH PRIMARY KEY  USING DEFAULT LOCAL ROLLBACK SEGMENT 
AS
SELECT 
   ......
   .....
FROM lbr_vw@remote 
where ...
;

begin
 dbms_mview.refresh('BPS_MV', 'c');
 ommit;
end;
/

ORA-1555   November 8, 2007 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Anne from MN,USA

Thanks Tom!.

Just want to make sure I understand this correct : "Your select is getting this, not the "insert" 
bit" -  From the tkprof, I see that the  BPS_MV refresh does a truncate + insert /*APPEND*/. 
So the "select" is the select query inside the bps_mv, right ?

The rollback for the insert (part of the refresh) exists on the local database, right ?

How about the rollback for the query in the mv - the mv resides on local database, but query is 
from table@remote, so does the rollback for the query exist on the remote db ?

As always, you help is so valuable! Thank you.

ORA-1555   November 13, 2007 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Anne from MN, USA

begin
 dbms_mview.refresh('BPS_MV', 'c');
 commit;
end;
/

http://asktom.oracle.com/pls/ask/search?p_string=delayed+block+cleanout

Very interesting!   November 16, 2007 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Anne from MN, USA

1. create table bps_hours as select * from bps_mv;

2. drop bps_mv;

3. create materialized view bps_mv
.....
REFRESH COMPLETE   ON DEMAND   with ROWID  USING DEFAULT LOCAL ROLLBACK SEGMENT 
AS
SELECT ....
  FROM lbr_vw@remote 
where NVL(pay_end_dt,sysdate) > add_months(sysdate, -15) -- pay_end_dates up to the previous 15 
months
and (<fld1, fld2, fld3) not in 
         (select/*+ HASH_AJ */  fld1, fld2,fld3           
      --basically pk combo
            from bps_hours
           where pay_end_dt > add_months(sysdate, -15)
             and pay_end_dt is not null
           )
;

4. Refresh of mv will follow insert into the bps_hours table.
begin
 dbms_mview.refresh('BPS_MV', 'c');
 --Refresh table from snapshot
     insert into bps_hours
       select *
         from bps_mv
       where pay_end_dt >  add_months(sysdate, -15) 
        and (<fld1, fld2, fld3) not in 
            (select/*+ HASH_AJ */  fld1, fld2,fld3          
      --basically pk combo
            from bps_hours
           where pay_end_dt > add_months(sysdate, -15)
             and pay_end_dt is not null
           )

Rollback too small   November 28, 2007 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Anne from MN, USA

Yes Tom, that is exactly what I had at first "just a simple MV, no subqueries or anything."

But then I am getting the ORA-1555 error  rollback segment too small, on complete refresh 

That's why I came up with the idea of creating the mv with rows that are not in the local table, 
and then upon refresh of mv, do an insert into the local table from the mv.  So the local table 
will always have the entire data from OCT-2005 onwards, and the mv will be refreshing for just the 
newer records. I am hoping that this may get rid of the rollback segment too small error on refresh 
of mv. 

I would appreciate your thoughts. Thanks so much.