SAS Processing Error

[obendav]

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

[JosvanderVelden]

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]

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