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: 10694
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: 10694
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: Thu Oct 01 12:35:05 CDT 2020