profile中SESSIONS_PER_USER 資源限制特性測試

講課時隨手總結[@more@]

SQL> desc dba_profiles
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)

SQL> col limit format a2
SQL> select * from dba_profiles where profile='PROF_TEST'
2 and resource_name='SESSIONS_PER_USER';

PROFILE RESOURCE_NAME RESOURCE LI
------------------------------ -------------------------------- -------- --
PROF_TEST SESSIONS_PER_USER KERNEL 2

SQL> alter user test4 profile prof_test;

使用者已更改。

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
SQL>
--============================
Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization
parameter is set to TRUE. With RESOURCE_LIMIT at its default value of FALSE, profile
limitations are ignored

--============================
session 1:
C:>sqlplus test4/test4

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 10 15:06:41 2010

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

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
--============================
session 2:
C:>sqlplus test4/test4

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 10 15:06:41 2010

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

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
--============================

session 3:
C:>sqlplus test4/test4

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 10 15:07:01 2010

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

ERROR:
ORA-02391: 超出同時存在的 SESSIONS_PER_USER 限制

請輸入使用者名稱:

--==========================

最後介紹2個view:

1、user_password_limits --檢視使用者的password限制

SQL> select * from user_password_limits;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME .0003
PASSWORD_REUSE_TIME .0006
PASSWORD_REUSE_MAX 2
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME .0006
PASSWORD_GRACE_TIME 1

已選擇7行。

user_resource_limits --檢視使用者的resource限制

SQL> select * from user_resource_limits;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

已選擇9行。

SQL>