使用resource_limit及profile限制用户连接(二)
APP_USER IDLE_TIME KERNEL 3
APP_USER CONNECT_TIME KERNEL DEFAULT
APP_USER PRIVATE_SGA KERNEL DEFAULT
APP_USER FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
APP_USER PASSWORD_LIFE_TIME PASSWORD DEFAULT
APP_USER PASSWORD_REUSE_TIME PASSWORD DEFAULT
APP_USER PASSWORD_REUSE_MAX PASSWORD DEFAULT
APP_USER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
APP_USER PASSWORD_LOCK_TIME PASSWORD DEFAULT
APP_USER PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
3、演示资源被限制的情形
[sql]
C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:10 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> host ----->开启一个session
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1 --->尝试开启另一个sessioin
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:21 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit --->此时收到资源被限制的提示
Enter user-name:
--在服务器端查看session的情形，3分钟后用户scott 的session的状态被置为SNIPED
SQL> @comm_sess_users;
+----------------------------------------------------+
| User Sessions (All) |
+----------------------------------------------------+
Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine
--------- ------ --------- --------- ----------- ------------ -------- --------------------- ---------- -------------------
oradb 1 5 INACTIVE SYS oracle 10090 sqlplus@node1.szdb.co pts/1 node1.szdb.com
35 7 ACTIVE HR robin 10171 sqlplus@SZDB (TNS V1- pts/2 SZDB
40 237 SNIPED SCOTT Robinson.Che 13282 sqlplus.exe PC39 2GOTRADESZ\PC39
--获得session的spid
SQL> @my_spid_from_sid
Enter value for input_sid: 40
SID SERIAL# SPID
------ ---------- -------------------------------------
40 237 13282
--此时的时间为20:17:54
SQL> ho date
Wed Jun 26 20:17:54 CST 2013
--查看scott对应的server process，其进程的启动时间为18:12，过了1个多小时，进程依旧没有被释放
SQL> ho ps -ef | grep 13282 | grep -v grep
oracle 13282 1 0 18:12 00:00:00 oracleoradb (LOCAL=NO)
--下面调用shell脚本来杀掉对应的进程
SQL> host
[oracle@node1 ~]$ ./kill_sniped.sh oradb
13282
[oracle@node1 ~]$ ps -ef | grep 13282 | grep -v grep
--清除服务器进程的shell脚本
[oracle@node1 ~]$ more kill_sniped.sh
#!/bin/sh
export ORACLE_SID=$1
tmpfile=/tmp/tmp.$$