Oracle:设置查询超时 [英] Oracle: Set Query Timeout

问题描述

我有一个PL/SQL程序，该程序通过透明网关对AS400数据库进行查询.有时AS400不响应查询(可能是网络问题)，并且PL/SQL程序挂起.

是否有任何方法可以为Oracle查询设置超时，以便在经过一定时间后会引发异常?

解决方案

您是否尝试过在AS400透明网关初始化文件中设置HS_FDS_CONNECT_PROPERTIES参数?

超时2分钟:

HS_FDS_CONNECT_PROPERTIES="timeout='120'"

设置查询超时的另一个更通用的选择是创建一个配置文件，并将其分配给运行查询的用户.

资源配置文件可用于设置任何特定会话中各种使用的限制-一个可用的资源限制是连接时间.

例如，您可以创建一个配置文件as400_tg_profile并为其分配最长2分钟的连接时间:

create profile as400_tg_profile limit connect_time 2;

...，那么您可以将此配置文件分配给运行查询的用户:

alter user as400_tg_user profile as400_tg_profile;

创建配置文件的选项很多，有多种方法可以将配置文件分配给特定用户，因此您应该通读文档.

如果需要动态分配特定的资源限制，您还可以考虑使用Oracle Resource Manager创建资源组和资源配置文件-这使您可以细粒度地控制各个会话的资源.

Oracle文档确实非常有用-对于初学者，请给它一个阅读:

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

更多详细信息:

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

这是在企业管理器中更易于使用的功能之一，但是在以下示例中提供了一个快速的PL/SQL示例:

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

i have a PL/SQL program which do a query to an AS400 database through Transparent Gateway. Sometimes the AS400 not responds to the query (may be network problems )and the PL/SQL program hangs.

Is there any method to set a timeout to the Oracle query so that when certain amount of time passes an exception is risen?

解决方案

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

For a timeout of 2 minutes:

HS_FDS_CONNECT_PROPERTIES="timeout='120'"

Another more general option for setting a query timeout is to create a profile and assign it to the user running your query.

A resource profile can be used to set limits on all sorts of usage in any particular session - one resource limit available is connection time.

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

create profile as400_tg_profile limit connect_time 2;

... then you could assign this profile to the user running the query:

alter user as400_tg_user profile as400_tg_profile;

There are lots of options on creating a profile and there are many ways to assign a profile to a particular user so you should read through the documentation.

You could also look into using Oracle Resource Manager creating resource groups and resource profiles if you need to dynamically assign particular resource limits - this gives you fine-grained control of resources for individual sessions.

The Oracle documentation is really good on this - for starters, give this a read:

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

For more detail:

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

This is one of those bits of functionality that's 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

这篇关于Oracle:设置查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！