SAS/Access Oracle timeout

Occasional Contributor
Posts: 12

SAS/Access Oracle timeout



we face an issue with SAS/Access to Oracle when we do some processing on SAS Datasets then get back to Oracle connection 

ORA-03135, ORA-03134 session timeout 

we have an issue that we can't increase session timeout as the client refuse to do this 

they request to redesign our work we don't have anytime to do this 

is there is any option which makes connection always open like ping every specifc time or whatever 


appericate your help 

Posts: 509

Re: SAS/Access Oracle timeout

Posted in reply to Amahareek

we faced similar issue one year ago, where one of the query was taking long and often they were connection time out. This has to do with Oracle settings, where in connections settings has to changed at oracle side. Oracle team refused to change the settings and We finally optimized our query to circumvent the issue.

Trusted Advisor
Posts: 1,837

Re: SAS/Access Oracle timeout

Depending on resources and on the work type - 

can you import the relevant data from oracle to a sas table and then use it instead the oracle table ?

Super User
Posts: 10,778

Re: SAS/Access Oracle timeout

Posted in reply to Amahareek

Maybe your Oracle is hibernated .

Respected Advisor
Posts: 4,736

Re: SAS/Access Oracle timeout

[ Edited ]
Posted in reply to Amahareek


"We have not time to..." is a weak argument in a consulting situation.


What I probably would do:

1. Some Internet research what the default/best practice timeout settings for Oracle are. If your customer is clearly below and hasn't specified a requirement that your processes must run within a defined time then you've got ammunition to make a case (=dear customer either increase the timeout setting for our batch user or find additional money/time for changes required for your site speciflc but not specified and not best practice timeout settings).

2. Try to understand why your customer says "no" and don't take the first answer as the final answer. It needs sometimes a bit of explaining why the general rule doesn't apply for a specific case.

3. Talk to the right people. Only because some ivory tower techy guy says no doesn't mean that this is the final decision. Convince the person who can overrule the initial "no" - and that's normally a senior manager on the business side who's in the end generating the salary for the IT guy and the IT guy's manager.

4. Performance improve your code! Sometimes that's quite simple; i.e.if it's pass-through SQL then using hints to parallelize execution on the Oracle side can make quite a difference. You can eventually also ask the customer's DBA to optimize your Oracle code.



when we do some processing on SAS Datasets then get back to Oracle connection 

How much effort would it be to change your code to create a SAS data set and then establish a new Oracle connection?


My experience with customers:

If you can explain why you need something changed and this makes business sense then there is almost always a solution. The customer will help you and even accept extra cost and/or delay.

But if you just try to get away with bad code/a bad solution then you're in trouble and you will eventually have to absorb the extra cost yourself and/or loose reputation.

Occasional Contributor
Posts: 9

Re: SAS/Access Oracle timeout

Posted in reply to Amahareek

I already found this problem on some clients and SAS environments using SGDBs like Oracle, Sybase or SQL Server and the solution was the same for all issue.

Data Bank settings for response after a long time.

My recomendation is to use SAS passthrough connection, because all process will be resolved on data bank tier with a minor time and minor "I/O" traffic, or talk with your DBA for change the Oracle settings.


Best regards

Ask a Question
Discussion stats
  • 5 replies
  • 1 like
  • 6 in conversation