Geek Answers Handbook

Oracle: setting request timeout

I have a PL / SQL program that queries a AS400 database through a Transparent Gateway. Sometimes the AS400 does not respond to the request (there may be network problems) and the PL / SQL program freezes.

Is there any method for setting a timeout on an Oracle request so that an exception occurs when a certain amount of time occurs?

+4
source share
1 answer

Have you tried setting the HS_FDS_CONNECT_PROPERTIES parameter in the AS400 Transparent Gateway initialization file?

Within 2 minutes:

 HS_FDS_CONNECT_PROPERTIES="timeout='120'"

Another common parameter for setting the request timeout is to create a profile and assign it to the user executing your request.

The resource profile can be used to set restrictions on all types of use in any particular session - one available resource is the connection time.

For example, you can create an as400_tg_profile profile and assign it a maximum connection time of 2 minutes:

 create profile as400_tg_profile limit connect_time 2;

... then you can assign this profile to the user executing the request:

 alter user as400_tg_user profile as400_tg_profile;

There are many options for creating a profile, and there are many ways to assign a profile to a specific user so that you can read the documentation.

You can also explore the use of Oracle Resource Manager, which creates resource groups and resource profiles if you need to dynamically assign specific resource restrictions - this gives you small-scale resource control for individual sessions.

The Oracle documentation is really good at this - for starters, give this the following:

http://www.oracle.com/technology/products/manageability/database/pdf/twp03/twp_oracle%20database%2010g%20resource%20manager.pdf

More details:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#ADMIN027

This is one of those bits of functionality that are easier to use in Enterprise Manager, but a quick PL / SQL example is given in:

http://www.dba-oracle.com/job_scheduling/resource_manager.htm

+4
source

More articles:


All Articles