BookmarkSubscribeRSS Feed
obendav
Calcite | Level 5

Guys,

 

I am running my SAS code in Oracle and in the past, I was able to trap the error message "ERROR: ORACLE execute error: ORA-01555: snapshot too old: rollback segment number 70 with name "_SYSSMU70_3114942609$" too small" when it was encountered and sent to the team. However, for the past month I haven't been able to do that per the log below because SAS system stopped processing. Please can anyone help with this? 

 

 

15730 +);
ERROR: ORACLE execute error: ORA-01555: snapshot too old: rollback segment number 70 with name "_SYSSMU70_3114942609$" too small.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
15731 +
15732 +%dm_check_status;
SYMBOLGEN: Macro variable SYSERR resolves to 0
SYMBOLGEN: Macro variable SQLXRC resolves to -1555
SYMBOLGEN: Macro variable SQLXMSG resolves to ORA-01555: snapshot too old: rollback segment number 70 with name
"_SYSSMU70_3114942609$" too small
SYMBOLGEN: Macro variable PMSG resolves to "Begin DM_CHECK_STATUS"
"Begin DM_CHECK_STATUS" 28AUG2023:14:36:21
SYMBOLGEN: Macro variable SYSERR resolves to 0
SYMBOLGEN: Macro variable LSYSERR resolves to 0
SYMBOLGEN: Macro variable LSQLXRC resolves to -1555
SYMBOLGEN: Macro variable LSQLXRC resolves to -1555
SYMBOLGEN: Macro variable LSQLXMSG resolves to ORA-01555: snapshot too old: rollback segment number 70 with name
"_SYSSMU70_3114942609$" too small
SYMBOLGEN: Macro variable PMSG resolves to "End DM_CHECK_STATUS"
"End DM_CHECK_STATUS" 28AUG2023:14:36:21
The SAS System

SYMBOLGEN: Macro variable SYSERR resolves to 0
15733 + %DM_LOG (PMSG="SQL command run with status:&SQLXRC.");
SYMBOLGEN: Macro variable SQLXRC resolves to -1555
SYMBOLGEN: Macro variable PMSG resolves to "SQL command run with status:-1555"
"SQL command run with status:-1555" 28AUG2023:14:36:21
SYMBOLGEN: Macro variable SYSERR resolves to 0
15734 +
15735 +** Disconnect from databases and close the files **;
15736 +DISCONNECT FROM ORADB_FSMS;
NOTE: Statement not executed due to NOEXEC option.
15737 +QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
NOTE: PROCEDURE SQL used (Total process time):
real time 3:20:47.95
cpu time 11.94 seconds

2 REPLIES 2
JosvanderVelden
SAS Super FREQ
The error message from Oracle is usually a secondary error, indicating that a query runs too long and exceeds timeout limit. Oracle wants to rollback but could not due to snapshot being too old. The reason it is too old is because the space allocated for snapshot is full.

If Oracle hint is used to speed up the query, make sure preserve_comments option is used in connect to statement.
LinusH
Tourmaline | Level 20

So your problem is that can't get hold of the log with this error message?

Maybe you can try using the LOGPARM system option:

"You use the WRITE= option of the LOGPARM= system option to configure when the SAS log contents are written. Set LOGPARM=“WRITE=IMMEDIATE” for the log content to be written as it is produced"

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p119kau8rt2ebgn1bzaipafu6jp3.htm

Data never sleeps

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 103 views
  • 1 like
  • 3 in conversation