BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 12c MAX_IDLE_TIME Tips

2019-07-15

Oracle 12c MAX_IDLE_TIME Tips

MAX_IDLE_TIME

We can set idle time to control the idle sessions from Oracle Database 12.2.

1. Max_idle_time is new parameter from 12.2.
2. alter system .... to set the idle time for whole DB(cdb/pdb), NOT for special session. (Can NOT use alter session ...).
3. max_idle_time's valule is NOT second, it is minuties. So the time is NOT accurate.

Test

There will be ORA-03113 error while the session has MAX_IDLE_TIME minuties.

# [ oracle@localhost:/home/oracle [02:49:37] [19.3.0.0.0 [DBMS EE] SID=orcl] 0 ] #
# sqlplus sys/oracle@localhost:1521/pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 17 02:49:51 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@localhost:1521/pdb1> show parameter max_idle_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_idle_time                        integer     0
SYS@localhost:1521/pdb1> alter system set max_idle_time=1;

System altered.

SYS@localhost:1521/pdb1> show parameter max_idle_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_idle_time                        integer     1
SYS@localhost:1521/pdb1> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

# [ oracle@localhost:/home/oracle [02:50:36] [19.3.0.0.0 [DBMS EE] SID=orcl] 0 ] #
# sqlplus soe/Welcome#2019@localhost:1521/PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 17 02:50:57 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 17 2019 02:49:13 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SOE@localhost:1521/PDB1> select * from dual;

D
-
X

SOE@localhost:1521/PDB1> set time on
02:51:16 SOE@localhost:1521/PDB1> select * from dual;

D
-
X

02:51:21 SOE@localhost:1521/PDB1>
02:51:21 SOE@localhost:1521/PDB1> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6417
Session ID: 237 Serial number: 46791


02:53:18 SOE@localhost:1521/PDB1>

Have a good work&life! 2019/07 via LinHong


Similar Posts

下一篇 Oracle Tools Tips

Comments