Oracle database is a very popular relational database, but during use, we often encounter the problem of database connection timeout. This problem may cause a series of troubles such as data transmission interruption and performance degradation. So, how to set the timeout in Oracle database? This article will introduce you to the specific method.

1. Principle of timeout setting

In Oracle, the timeout period is implemented through SQLnet read and write timeout parameters. SQLnet is a network component of Oracle Database that provides support for communication between clients and servers. When executing a timeout, the client issues a request. When the server does not respond, the client waits for the specified time. If the waiting time exceeds the specified timeout, the connection will be disconnected.

Normally, there are two types of Oracle timeout settings:

1. Session-level timeout

Session-level timeout is effective for a single session, through the ALTER SESSION command to set.

2.SQLNET level timeout

SQLNET level timeout is valid for all sessions of the entire SQLNET connection and is set by modifying the SQLNET.ORA file. SQLNET.ORA is Oracle's network configuration file, in which SQLNET-level parameters can be defined.

You can choose the appropriate timeout setting method according to the actual situation.

2. Session-level timeout setting

In Oracle, you can use the ALTER SESSION command to set the session-level timeout. The syntax is as follows:

ALTER SESSION SET SQL_TRACE = TRUE | FALSE;

Among them, SQL_TRACE = TRUE means enabling tracking mode. In this mode, Oracle database will record the execution of all SQL statements. .

ALTER SESSION SET SQL_TRACE = FALSE;

SQL_TRACE = FALSE means disabling tracing mode.

You can set the TIMED_STATISTICS parameter to true when SQL_TRACE = TRUE to enable the output of statistical information in tracking mode, as shown below:

ALTER SESSION SET TIMED_STATISTICS = TRUE;

For session-level timeouts, this can be achieved by setting the SQLNET.ALLOWED_LOGON_VERSION parameter to 10 or higher.

SQLNET.ALLOWED_LOGON_VERSION is a SQLNET.ORA parameter that defines which Oracle database versions can be authenticated. For example, when allowing Oracle 10g for authentication, you can set this parameter:

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10

This will allow clients connected to the Oracle 10g database to connect.

3. SQLNET level timeout setting

SQLNET level timeout is based on the parameter setting of the SQLNET.ORA file. The SQLNET.ORA file is usually located in a specific directory on the Oracle client or server.

In the SQLNET.ORA file, you can control the SQLNET level timeout by setting two parameters, namely SQLNET.INBOUND_CONNECT_TIMEOUT and SQLNET.OUTBOUND_CONNECT_TIMEOUT.

SQLNET.INBOUND_CONNECT_TIMEOUT is a parameter on the server side, used to control the time when the server accepts client sessions.

SQLNET.OUTBOUND_CONNECT_TIMEOUT is a parameter of the client, used to control the time when the client connects to the server.

By setting these two parameters, you can prevent long-term connections between the client and the server, thereby avoiding wasting too much time and resources on the connection.

Add the following parameters in the SQLNET.ORA file to set SQLNET.INBOUND_CONNECT_TIMEOUT:

INBOUND_CONNECT_TIMEOUT_LISTENER = 120

Set the timeout for the server to accept client sessions to 120 seconds.

Add the following parameters in the SQLNET.ORA file to set SQLNET.OUTBOUND_CONNECT_TIMEOUT:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10

Set the timeout for the client to connect to the Oracle server to 10 Second.

It should be noted that when modifying parameters by modifying the SQLNET.ORA file, you need to restart the Oracle database or Oracle listener to make the parameters take effect.

4. How to verify whether the timeout setting takes effect

After you complete the setting of the Oracle timeout time, how to verify whether it takes effect? This can be verified through Oracle's v$session dynamic view or by tracing SQL statements.

1. Verification through v$session view

You can view the current session of the user through the following SQL statement:

SELECT * FROM v$session WHERE sid = '&sid';

The &sid here is the SID of the current user, which can be obtained through the following SQL statement:

SELECT sid, serial# FROM v$session WHERE username = '&username' ;

After finding the specified SESSION, you can query the CONNECT_TIME and IDLE_TIME parameters of the SESSION to determine whether it is effective.

2. Trace SQL statements

If you need to trace the SQL statements of the Oracle database, you can set the TRACEFILE_IDENTIFIER parameter of SESSION and use the Oracle Trace tool to trace the SQL statements. It should be noted here that you need SYSDBA or SYSOPER permissions to perform tracking operations.

You can verify whether the Oracle timeout settings are valid through the above two methods. If the settings are found to be invalid, you can troubleshoot by checking whether the SQLNET.ORA file is configured completely and correctly.

Summarize

In the Oracle database, since long-term connections will affect system performance and other factors, the connections need to be controlled. Through the ALTER SESSION command and the settings of the SQLNET.ORA file introduced in this article, you can set the timeout for the Oracle database. Of course, in actual use, it is necessary to choose the appropriate timeout setting method according to different environments and actual needs.

Note: This article refers to many excellent materials. If there is any infringement, please contact us to delete it.

The above is the detailed content of How to set timeout in Oracle database. For more information, please follow other related articles on the PHP Chinese website!