Home » RDBMS Server » Performance Tuning » URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSS
URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSS [message #65679] Thu, 02 December 2004 21:29 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Experts,

I have this procedure which gives me Error during execution.

CREATE OR REPLACE PROCEDURE ARSP_CHECK_ADDED_BY_TCT
as

-- Temp variables

tmpEmpolyeeId  Number;
tmpCreated_By  Varchar2(100);
tmpTablePk   Number;
tmpTablePk1   Number;
tmpAdded_By_c  Varchar2(100);
tmpCheckNumChar  Number;

CURSOR tmpCursor IS
SELECT TRANS.COMM_LOG_ID_C,TRANS.COMM_SEQ_UNQ_Q,TRANS.ADDED_BY_C,DECODE(REPLACE(TRANSLATE(trim(TRANS.ADDED_BY_C),'0123456789', '0000000000'), '0'),NULL, '0', '1')
FROM CUS.TCOMMUNICATION_LOG@DT.LINK L, CUS.TCOMM_TRANSMITTAL@DT.LINK TRANS WHERE L.COMM_LOG_ID_C = TRANS.COMM_LOG_ID_C AND (L.COMP_REP_ID_C IS NULL OR EXISTS (SELECT 'X' FROM CUS.TCOMP_REP@DT.LINK R, CUS.TACCOUNT@DT.LINK ACCT, CUS.TORGANIZATION@DT.LINK ORG WHERE R.COMP_REP_ID_C = L.COMP_REP_ID_C AND R.SVC_C IN ('000020','000021','000056','000356','000057','000048','000046','008203','000148', '008201','000228','000223','000047','000054','000049','008204','000154','008202','000229','000227','000053', '000560','000160', '000161','000005','000006') AND  ACCT.ORG_ID_C = R.ORG_ID_C AND ACCT.ORG_NME_SEQ_C = R.ORG_NME_SEQ_C AND ORG.ORG_ID_C = ACCT.ORG_ID_C AND ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C AND ACCT.ACCT_TYP_C = 'COMP' AND ACCT.ACCT_STAT_C NOT IN ('POTENL','ACTWSB') AND ORG.JURIS_ID_C <> '70001' )) AND ((L.ORG_ID_C IS NULL AND L.ORG_NME_SEQ_C IS NULL) OR EXISTS (SELECT 'X' FROM CUS.TACCOUNT@DT.LINK ACCT, CUS.TORGANIZATION@DT.LINK ORG, CUS.TORGANIZATION@DT.LINK ACCT_ORG, CUS.TCOMP_REP@DT.LINK R WHERE ORG.ORG_ID_C = L.ORG_ID_C AND ORG.ORG_NME_SEQ_C = L.ORG_NME_SEQ_C AND ORG.JURIS_ID_C <> '70001' AND ACCT_ORG.ORG_ID_C = ORG.ORG_ID_C AND ACCT_ORG.JURIS_ID_C <> '70001' AND ACCT_ORG.ORG_ID_C = ACCT.ORG_ID_C AND ACCT_ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C AND ACCT.ACCT_TYP_C = 'COMP' AND ACCT.ACCT_STAT_C NOT IN ('POTENL','ACTWSB') AND R.ORG_ID_C = ACCT_ORG.ORG_ID_C AND R.ORG_NME_SEQ_C = ACCT_ORG.ORG_NME_SEQ_C AND R.SVC_C IN ('000020','000021','000056','000356','000057','000048','000046','008203','000148', '008201','000228','000223','000047','000054','000049','008204','000154','008202','000229','000227','000053', '000560','000160', '000161','000005','000006'))) AND L.JURIS_ID_C <> '70001';

Begin

DELETE FROM TMP_CHECK_ADD_MODIFY WHERE TABLE_NAME='TCOMM_TRANSMITTAL_ADD';
COMMIT;

OPEN tmpCursor;
LOOP
FETCH tmpCursor INTO tmpTablePk,tmpTablePk1,tmpAdded_By_c,tmpCheckNumChar;
EXIT WHEN tmpCursor%NOTFOUND;

 BEGIN
  SELECT CREATED_BY INTO tmpCreated_By FROM TCOMM_TRANSMITTAL WHERE COMM_TRANSMITTAL_ID = tmpTablePk * 100 + tmpTablePk1;

  IF tmpCheckNumChar = 0 then

   BEGIN
    SELECT SERVICE_TEAM_MEMBER_ID INTO tmpEmpolyeeId FROM AV_SERVICE_TEAM_MEMBER_KEYS WHERE DESKTOP_ID IS NOT NULL AND DESKTOP_ID = NVL(tmpAdded_By_c,0);

    If tmpCreated_By != to_char(tmpEmpolyeeId) then
     INSERT INTO TMP_CHECK_ADD_MODIFY VALUES('TCOMM_TRANSMITTAL_ADD',tmpTablePk,tmpCreated_By,tmpAdded_By_c);
     Commit;
    End If;

   EXCEPTION
   WHEN NO_DATA_FOUND THEN
    If TRIM(tmpCreated_By) != 'CONV04' Then
     INSERT INTO TMP_CHECK_ADD_MODIFY VALUES ('TCOMM_TRANSMITTAL_ADD',tmpTablePk,tmpCreated_By,tmpAdded_By_c);
     Commit;
    End If;
   END;

  ELSIF tmpCheckNumChar = 1 then
   If TRIM(tmpCreated_By) != TRIM(tmpAdded_By_c) Then
    INSERT INTO TMP_CHECK_ADD_MODIFY VALUES ('TCOMM_TRANSMITTAL_ADD',tmpTablePk,tmpCreated_By,tmpAdded_By_c);
    Commit;
   End If;
  END IF;

 EXCEPTION
 WHEN NO_DATA_FOUND THEN
  NULL;
 END;
END LOOP;
CLOSE tmpCursor;

END;

The Error is :-

ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSSMU17$" too small

ORA-02063: preceding line from DT.LINK

ORA-06512: at "ARROW.ARSP_CHECK_MODIFIED_BY_TCT", line 26

ORA-06512: at line 1

I am using Auto UNDO mode and Version is 9.2.0.5. Can any body suggest me any change in code or solution for this problem.

Any help is appreciated.

Thanks in advance

Milind.
Re: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_ [message #65680 is a reply to message #65679] Fri, 03 December 2004 00:20 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi,
Maybe your UNDO-Tablespace is filled up so the Undo-Segment 17 could not grow up ?

ciao
Uwe
Re: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_ [message #65681 is a reply to message #65680] Fri, 03 December 2004 01:12 Go to previous messageGo to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi,

Thanks for the reply.

Do you mean my UNDO segment 17 could not grow means the space in the UNDO tablespace datafile is filled up.
But, I have kept AUTO EXTEND on for the datafile.I have only one datafile for UNDO tablespace.

Can you suggest me the measures stepwise that what should be done in this situation.

Thanks in advance

Milind.
Re: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_ [message #65682 is a reply to message #65681] Fri, 03 December 2004 02:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Auto extend ON will not create a new datafile for Undo operations.

Your Undosegments are not sized properly.
either increase it ( or add another undo file for the tablespace)
ALTER TABLESPACE undotbs  ADD DATAFILE '.......your path' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;


Other option ( the first you should try ) is
to set the undo_rentention ( it an unix in seconds).

If the UNDO_RETENTION initialization parameter is not specified, the default value is 900 seconds.

this would be the command.
ALTER SYSTEM SET UNDO_RETENTION = 30000;

quoting docs

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. But for consistent read purposes, long running queries might require old undo information for undoing changes and producing older images of data blocks. The initialization parameter, UNDO_RETENTION, provides a means of explicitly specifying the amount of undo information to retain. With a proper setting, long running queries can complete without risk of receiving the "snapshot too old" error.
Re: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_ [message #65683 is a reply to message #65682] Fri, 03 December 2004 02:46 Go to previous messageGo to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Thanks a lot for your quick reponse.
Re: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_ [message #65684 is a reply to message #65683] Fri, 03 December 2004 02:50 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And There are too much of commits in your Block.
remove them and give one final commit.
see whether there is any difference.~!
Previous Topic: Performance
Next Topic: DB Optimization
Goto Forum:
  


Current Time: Sun Dec 22 23:18:32 CST 2024