这篇博客是ORACLE profile系列的第四篇，主要说一下，如果创建profile和使用profile进行资源和密码控制

CREATE PROFILE

Note:

Purpose

Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.

See Also:

Prerequisites

To create a profile, you must have the CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.

Create a profile that defines the limits using the CREATE PROFILE statement

Assign the profile to the user using the CREATE USER or ALTER USER statement

##创建并使profile生效的前提条件是：

要想成功创建profile，用户必须具有create profile权限

如果想使profile中指定的限制对相关用户生效，首先我们需要把该profile指定给用户，其次我们需要开启数据库的resource_limit功能。(可以在数据库启动之前在参数文件中指定RESOURCE_LIMIT初始化参数，或者直接使用alter system set resource_limit=true;来启用)

See Also:

ALTER SYSTEM for information on enabling resource limits dynamically

Oracle Database Reference for information on the RESOURCE_LIMIT parameter

CREATE USER and ALTER USER for information on profiles

Syntax

create_profile::=

resource_parameters::=

(size_clause::=

password_parameters ::=

Examples

Creating a Profile: Example The following statement creates the profile new_profile:

CREATE PROFILE new_profile
  LIMIT PASSWORD_REUSE_MAX 10
        PASSWORD_REUSE_TIME 30;

Setting Profile Resource Limits: Example The following statement creates the profile app_user:

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

If you assign the app_user profile to a user, then the user is subject to the following limits in subsequent sessions:

The user can have any number of concurrent sessions.

In a single session, the user can consume an unlimited amount of CPU time.

A single call made by the user cannot consume more than 30 seconds of CPU time.

A single session cannot last for more than 45 minutes.

In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.

A single call made by the user cannot read more than 1000 data blocks from memory and disk.

A single session cannot allocate more than 15 kilobytes of memory in the SGA.