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>