PL/SQL Client connects Oracle quickly to resolve disconnection issues

PL/SQL login for a short time session automatically disconnects

1. First look at your profile file for this user

Select profile from dba_users where username= ' username ';

2. Review the Idle_time settings in the profile file

SELECT * from Dba_profiles where profile= ' profilename ';

3. If the Idle_time value found in the second step is small, modify it to unlimited without limiting

Alter profile Prfilename idle_time=unlimited;

4. If you have changed, automatically disconnect check the settings of the PL/SQL tool

Tools->preferences->connection: Tick Check Connection

http://blog.csdn.net/rgb_rgb/article/details/8073030

[Python]View Plaincopyprint?

2. Sgcrm5*sgcrm5-/home/oracle >sqlplus/as SYSDBA
6. Sql*plus:release 11.1. 0.6.0-production on Mon Oct : £º
10. Copyright (c) 1982, Oracle.  All rights reserved.
16. Connected to:
18. Oracle Database 11g Enterprise Edition Release 11.1. 0.6.0- 64bit Production
20. With the partitioning, OLAP, Data Mining and Real application testing Options
24. [Email protected] 129.100.253.79> Show parameter Resource_limit
28. NAME TYPE VALUE
30. ------------------------------------ ----------- ------------------------------
32. Resource_limit Boolean FALSE
34. [Email protected]  129.100.253.79> alter system set RESOURCE_LIMIT=TRUE Scope=both;
38. System altered.
42. Elapsed: xx:00.08
44. [Email protected] 129.100.253.79> Select Username,profile from dba_users
46. 2;
50. USERNAME profile
52. ------------------------------ ------------------------------
54. 9959 DEFAULT
56. SCOTT DEFAULT
58. SPOTLIGHT DEFAULT
60. SPATIAL_WFS_ADMIN_USR DEFAULT
62. Mddata DEFAULT
64. Xs$null DEFAULT
66. Apex_public_user DEFAULT
68. Tsmsys DEFAULT
70. DIP DEFAULT
72. SPATIAL_CSW_ADMIN_USR DEFAULT
74. ORACLE_OCM DEFAULT
76. Owbsys DEFAULT
78. Exfsys DEFAULT
80. flows_030000 DEFAULT
82. Flows_files DEFAULT
84. Wk_test DEFAULT
86. Dbsnmp Monitoring_profile
88. Wmsys DEFAULT
90. Ordsys DEFAULT
92. Wksys wksys_prof
94. XDB DEFAULT
96. Olapsys DEFAULT
98. Si_informtn_schema DEFAULT
100. Ordplugins DEFAULT
102. Wkproxy DEFAULT
104. Mdsys DEFAULT
106. Sysman DEFAULT
108. Ctxsys DEFAULT
110. ANONYMOUS DEFAULT
112. SYS DEFAULT
114. SYSTEM DEFAULT
116. Mgmt_view DEFAULT
118. Outln DEFAULT
122. Rows selected.
126. Elapsed: xx:00.05
128. [Email protected]  129.100.253.79> SELECT distinct from dba_profiles;
132. Profile
134. ------------------------------
136. Wksys_prof
138. DEFAULT
140. Monitoring_profile
142. PROFILE9959
146. Elapsed: xx:00.01
148. [Email protected] 129.100.253.79> SELECT Name, value
150. 2 from Gv$parameter
152. 3 WHERE name = ' Resource_limit ';
156. NAME
158. --------------------------------------------------------------------------------
160. VALUE
162. --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------
164. Resource_limit
166. TRUE
172. Elapsed: xx:00.01
174. [Email protected]  129.100.253.79> Alter user system profile PROFILE9959;
178. User altered.
182. Elapsed: xx:00.07
184. [Email protected] 129.100.253.79>

Attention:

After the setting is complete, the session that is already connected does not work and the session on the new connection takes effect.

Use the alter system dynamically or use the initialization parameter Resource_limit to make resource restrictions effective. The change is not valid for the password resource and the password resource is always available.

After setting idle_time in the database profile, if the session exceeds this limit, the Pmon process will:

[SQL]View Plaincopyprint?

1. * Mark the v$session as sniped
2. * Clean up the database resources for the session
3. * Remove the V$session entry

There are often sniped-state sessions, which are inactive, but occupy the number of users ' sessions:

Select Sid,serial#,paddr,username,status from v$session where status = ' sniped ';

Here we see the phenomenon is v$session, the session has not been cleaned. In the best case scenario, when the user tries to connect to the session again, they receive a ORA-02396 error, and the session is cleaned up.

Attempting to use alter system kill session,v$session marked as killed,v$process, OS process is not cleaned up.

[Python]View Plaincopyprint?

1. Select Type,s.username,s.status,s.machine,s.program,' alter system kill session ' | | "'" | | s.sid| | ', ' | | s.serial# | | '; ' Oracle_level_kill,paddr from v$session s where status = ' killed '
2. Select ' kill-9 ' | |  SPID Unix_level_kill from v$process where addr=' 07000002309e5be8 ';

Kill-9 on OS, session is cleaned up.

[Python]View Plaincopyprint?

2. DROP profile PROFILE9959 CASCADE;
6. CREATE Profile PROFILE9959 LIMIT
8. Sessions_per_user UNLIMITED
10. Cpu_per_session UNLIMITED
12. Cpu_per_call UNLIMITED
14. Connect_time UNLIMITED
16. Idle_time
18. Logical_reads_per_session UNLIMITED
20. Logical_reads_per_call UNLIMITED
22. Composite_limit UNLIMITED
24. PRIVATE_SGA UNLIMITED
26. Failed_login_attempts Ten
28. Password_life_time
30. Password_reuse_time UNLIMITED
32. Password_reuse_max UNLIMITED
34. Password_lock_time 1
36. Password_grace_time 7
38. Password_verify_function NULL;

Profile: The name of the configuration file. The Oracle Database forces resource throttling in the following ways:

1. If the user exceeds the session resource limit of Connect_time or idle_time, the database rolls back the current transaction and ends the session. The user executes the command again, and the database returns an error,

2. If the user tries to perform an operation that exceeds the other session resource limit, the database discards the operation, rolls back the current transaction, and returns an error immediately. After the user can commit or roll back the current transaction, the session must end.

Tip: You can limit the time by dividing one piece into multiple segments, such as 1 hours (1/24 days), and you can specify resource limits for the user, but the database will not enforce the limit until the parameters are valid.

Unlimited: The user assigning this profile has unrestricted access to the resource, and when the password parameter is used, Unlimited means that there is no restriction on the parameters.

Default: Specifying default means ignoring some resource restrictions on the profile, and the default profile initial definition is not limited to resources and can be changed by the Alter Profile command.

Resource_parameter part

Session_per_user: Specifies the number of concurrent sessions that limit the user.

Cpu_per_session: Specifies the CPU time limit for the session, in 1% seconds.

Cpu_per_call: Specifies the CPU time limit for one invocation (parse, execute, and fetch) in 1% seconds.

Connect_time: Specifies the total connection time, in minutes, for the session.

Idle_time: Specifies the total time, in minutes, that the session is allowed to be continuously inactive, and the session will be disconnected. However, long-running queries and other operations are not subject to this limitation.

Logical_reads_per_session: Specifies the number of blocks of data that a session allows to read, including all blocks of data read from memory and disk.

Logical_read_per_call: Specifies the maximum number of data blocks that are allowed to be read by the Execute SQL (parse, execute, and fetch) call at one time.

PRIVATE_SGA: Specifies the maximum amount of space, in bytes, that a session can allow to allocate in the shared pool (SGA). (This restriction is only valid when using the shared server structure, where the private space of the session in the SGA includes both private and PL/SQL, but not shared SQL and PL/SQL).

Composite_limit: Specifies the total resource consumption for a session, expressed in service units units. Oracle database calculates cpu_per_session,connect_time,logical_reads_per_session and PRIVATE-SGA total service units in a profitable way

Password_parameter section:

Failed_login_attempts: Specifies the maximum number of times that an attempt to log in is allowed before the account is locked out.

Password_life_time: Specifies the number of days that the same password is allowed to use. If the Password_grace_time parameter is specified at the same time, if the password is not changed within grace period, the password is invalidated and the connection database is rejected. If the Password_grace_time parameter is not set, the default value of unlimited raises a database warning, but allows the user to continue the connection.

Password_reuse_time and Password_reuse_max: These two parameters must be associated with each other, password_reuse_time specifies the number of days before the password cannot be reused, and password_reuse_ MAX Specifies the number of times the password has changed before the current password is reused. Two parameters must be set to an integer.

1. If an integer is specified for both parameters, the user cannot reuse the password until the password has been changed Password_reuse_max the specified number of times after the specified amount of time in Password_reuse_time.

For example: password_reuse_time=30,password_reuse_max=10, the user can reuse the password after 30 days, requiring the password to be changed more than 10 times.

2. If one of these is specified as an integer and the other is unlimited, the user can never reuse a password.

3. If you specify one of the default values that are defined in profile for the Default,oracle database, all parameters are set to unlimited in profiles by default, and if you do not change the profile default value, The database always defaults to unlimited for this value.

4. If all two parameters are set to unlimited, the database ignores them.

Password_lock_time: Specifies the set time, in days, for the number of failed login attempts to reach payback households.

Password_grace_time: Specifies the number of days to expire, and the database issues a warning to the number of days before the login expires. If the database password is not modified in the middle, the expiration is invalidated.

Password_verify_function: This field allows complex PL/SQL password validation scripts to be passed as parameters to the Create profile statement. The Oracle database provides a default script, but you can create your own validation rules or use third-party software validation. For a function name, specifying the name of the password validation rule, which is specified as NULL, means that the password verification feature is not used. If you specify an expression for the password parameter, the expression can be in any format except for the database-scalar subquery.

Refer to: http://psoug.org/reference/profiles.html

PL/SQL Client connects Oracle quickly to resolve disconnection issues