Creation of Profile in Oracle Pluggable Database

Back to: Oracle DBA Tutorials

Creation of Profile in Oracle Pluggable Database

In this article, I am going to discuss the Creation of Profiles in Oracle Pluggable Database with Examples. Please read our previous article where we discussed the Creation of a Common Profile in Oracle with Examples.

Creation of Profile in Oracle Pluggable Database

Previously we learned how to create a common profile. Now, let’s try to learn how to create a user profile in the pluggable database.

Step 1: Connect to Pluggable Database

Open Linux virtual machine and start the listener and start the database. Once the database is started, open the SQL Developer, and connect to the database as “sqlplus / as sysdba”. Let’s go ahead and check the database name and the user.

Let’s go ahead and connect to the pluggable database.

Step 2: Check the Profiles

Let us go ahead and check the profile name with default.

Query: select * from dba_profiles where profile=’DEFAULT’;

So, these are the parameters for the default profile. So, this shows that there will be a default profile in the pluggable database also. We have values of both kernel and as well as password.

Step 3: Create a Profile.

Let us go ahead and create a profile in the pluggable database. We are going to name that profile JAYAPDB_PROFILE.

Statement:

Create profile JAYAPDBPROFILE
Limit
SESSIONS_PER_USER UNLIMITED
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 10
INACTIVE_ACCOUNT_TIME 10;

Let’s go ahead and run this statement.

We got an error. The error shows an invalid profile limit INACTIVE_ACCOUNT_TIME. The error number is 02377. This error indicates that we have mentioned the wrong value. In order to know more about this error, we can open a terminal and check about this error. There is a command to check the error details.

Command: ! oerr ora 2377

We can see the value of parameter INACTIVE_ACCOUNT_TIME should not be less than 15. We can find any details of any error with the number. We have mentioned 10. Let’s go ahead and change the value to 20.

Statement:

Create profile JAYAPDBPROFILE
Limit
SESSIONS_PER_USER UNLIMITED
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 10
INACTIVE_ACCOUNT_TIME 20

Let’s go ahead and run this statement.

So, we can see the profile is created. Let’s go ahead and check the profile.

Query: select * from dba_profiles where profile=’JAYAPDBPROFILE’;

So, the profile is created. The values are the default for some parameters and some values are defined by us.

Step 4: Create a user and assign the profile to the user.

Let us go ahead and create a user and assign privileges and assign the profile to the user. So, we are going to assign a profile while creating the user itself.

Statement: create user pdbnewuser identified by goodluck profile jayapdbprofile;

So, the user is created and the profile is assigned to the user. Let’s go ahead and grant some privileges to the user.

Statement: grant create session, create table, unlimited tablespace to pdbnewuser;

Let’s go ahead and check the status of the user and profile in the dba_users

Query: select * from dba_users where username=upper(‘PDBNEWUSER’);

We can see the user is created and the profile is mentioned as JAYAPDBPROFILE. So, this is how we create a profile in the pluggable database and assign the profile to the user.

In the next article, I am going to discuss Password Verification in Oracle with Examples. Here, in this article, I try to explain the Creation of Profile in Oracle Pluggable Database with Examples. I hope you enjoy this Creation of Profile in the Oracle Pluggable Database article.