1、修改初始化参数文件resource_limit,使资源限制生效。
SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=true scope=both; (这一条在alert日志中会有记录)
System altered
注意:无论资源显示是否生效,密码资源总是可用的。
2、使用create profile创建一个定义对数据库资源进行限制的profile
SQL> create profile scott_profile
limit password_reuse_max 10
password_reuse_time 30;
Profile created
注释:默认的profile名为:DEFAULT
3、使用create user或者alter user命令把profile分配给用户
SQL> alter user scott profile scott_profile;
User altered
4、修改资源配置profile
SQL> alter profile scott_profile
limit
password_reuse_time 90
password_reuse_max unlimited;
Profile altered
5、查询当前资源限制信息
SQL> SELECT profile,resource_name,resource_type,limit from dba_profiles ORDER BY PROFILE;
SQL> SELECT profile,resource_name,resource_type,limit from dba_profiles where profile='SCOTT_PROFILE';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
--------------- -------------------------------- ------------- ----------------------------------------
SCOTT_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
SCOTT_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
SCOTT_PROFILE CPU_PER_SESSION KERNEL DEFAULT
SCOTT_PROFILE CPU_PER_CALL KERNEL DEFAULT
SCOTT_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
SCOTT_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
SCOTT_PROFILE IDLE_TIME KERNEL DEFAULT
SCOTT_PROFILE CONNECT_TIME KERNEL DEFAULT
SCOTT_PROFILE PRIVATE_SGA KERNEL DEFAULT
SCOTT_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
SCOTT_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
SCOTT_PROFILE PASSWORD_REUSE_TIME PASSWORD 90
SCOTT_PROFILE PASSWORD_REUSE_MAX PASSWORD UNLIMITED
SCOTT_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
SCOTT_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
SCOTT_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected