Use profile to restrict user resources

This is really good for Oracle. If the application write is poor, resulting in too many connections, the database or listener may have problems. You can use profile to limit the number of concurrent connections of a user.

Let's take a look at the settings of the initialization parameter resource_limit. If it is false, profile does not take effect.

Alter system set resource_limit = true scope = both;

Create a profile:

Create profile DEFAULT1 LIMIT
SESSIONS_PER_USER 3
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

Here, only SESSIONS_PER_USER is modified. Other Default values are used. In fact, many resources can be set.

Modify a user's profile:

Alter user user1 PROFILE DEFAULT1;

OK. Try again. An error is reported when the fourth session is created:ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Note that the current user session is not affected when the user profile is modified.