Oracle：设置查询超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1914286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Set Query Timeout
提问by Telcontar
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.
我有一个 PL/SQL 程序，它通过透明网关对 AS400 数据库进行查询。有时AS400不响应查询（可能是网络问题），PL/SQL程序挂起。
Is there any method to set a timeout to the Oracle query so that when certain amount of time passes an exception is risen?
是否有任何方法可以为 Oracle 查询设置超时，以便在经过一定时间后会引发异常？
采纳答案by Nick Pierpoint
Have you tried setting the HS_FDS_CONNECT_PROPERTIESparameter in the AS400 Transparent Gateway initialisation file?
您是否尝试过HS_FDS_CONNECT_PROPERTIES在 AS400 透明网关初始化文件中设置参数？
For a timeout of 2 minutes:
对于 2 分钟的超时：
HS_FDS_CONNECT_PROPERTIES="timeout='120'"
Another more general option for setting a query timeout is to create a profileand 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_profileand assign it a maximum connection time of 2 minutes:
例如，您可以创建一个配置文件as400_tg_profile并为其分配 2 分钟的最大连接时间：
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.
如果您需要动态分配特定的资源限制，您还可以考虑使用 Oracle Resource Manager 创建资源组和资源配置文件 - 这使您可以对各个会话的资源进行细粒度控制。
The Oracle documentationis really good on this - for starters, give this a read:
在Oracle文档是对这个真的很好-对于初学者来说，给这个读：
For more detail:
欲知更多详情：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#ADMIN027
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:
这是在企业管理器中更易于使用的功能之一，但在以下位置给出了一个快速的 PL/SQL 示例：
http://www.dba-oracle.com/job_scheduling/resource_manager.htm
http://www.dba-oracle.com/job_scheduling/resource_manager.htm
