OraFAQ Forum: Performance Tuning » URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "

Home » RDBMS Server » Performance Tuning » URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSS [message #65679]

Thu, 02 December 2004 21:29

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.

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator