Creating a new PDB for Tablespace Practice in Oracle

Back to: Oracle DBA Tutorials

Creating a new PDB for Tablespace Practice in Oracle

In this article, I am going to discuss Creating a new PDB for Tablespace Practice in Oracle. Please read our previous article where we discussed How to Create Tablespace in Oracle. Previously we understood some clauses regarding tablespaces. Before practically working on the tablespaces, we need to prepare our environment in order to work on tablespaces. We are going to create a new pluggable database to work on tablespaces and query about the tablespaces.

Note: To create a pluggable we have to start the database and listener. Once the database and listener are up, we have to open DBCA (Database Configuration Assistant) to create a database. We will perform the Creation of pluggable databases in steps.

Step 1: Open DBCA.

Open the Linux machine and open the terminal and enter the command “dbca”.

If you enter the command “dbca” then there will be a popup as above opening the DBCA.

Step 2: Manage Pluggable Database

Once the DBCA is opened you will be able to see the below options. Just select the option “manage pluggable database” and click Next.

Step 3: Create a New Pluggable Database

You will see the below options. If you need to create a pluggable database select “create a pluggable database” or if you need to drop a database select “delete pluggable database”. We are selecting “create pluggable database” and clicking Next.

Step 4: Select the Container Database.

Once you select the option to create a pluggable database and click next you will get the below page asking to select the container database. Therefore, we have only one container database we will be selecting the container database. We are selecting the JAYADB database. Leave the username and password column. Click Next.

Step 5: Create a Pluggable Database from PDB$SEED.

Once you click the next DBCA bring to the below page asking to select the pluggable database. You can select any database but we are using PDB$SEED since it is a template database to create multiple pluggable databases. Click Next.

Step 6: Pluggable Database Details.

Once you select the template database and click next the DBCA asks you to provide the pluggable database name and administrator username and password for the administrator. We are giving the pluggable database name as JAYAPDB2 and username as pdb2admin and password for the user pdb2admin.

Once you click next you will see a popup that password doesn’t match the oracle requirements. Ignore the popup and click next.

Click Yes and continue.

Step 7: Create a Pluggable Database Summary.

The Storage is a file system and the Database location is the default that is taken by DBCA and then it asks to create the Default user tablespace. Select the option. This is the default option. Because we will be working on the tablespaces. So, make sure to select the default user tablespace.

Step 8: Create a Database.

You can see create database summary which displays the pluggable database name as JAYAPDB2 and the pluggable database source as default and datafile location.

Click finish and the process of creating a pluggable database starts.

Wait for a few minutes until the process becomes 100%.

The database is successfully created. So, the pluggable database is created. Let’s go ahead and grant some privileges and test the connection of the database.

Step 1: Connect to the Database.

Let us connect to the CDB$ROOT database and then try to connect to the pluggable database. We will be connecting as “sqlplus / as sysdba”. Once connected we will be checking the username and container name.

So, we are now connected to the root database. Let us move to the pluggable database PDBTS.

Command: alter session set container=PDBTS;

Step 2: Grant Privileges to the Pluggable Database Admin.

So, we have created a pluggable database admin as pdbts_admin. We are going to grant dba access to the admin.

Statement: grant dba, create session to pdbts_admin;

Now, the user pdbts_admin is the admin of the pluggable database PDBTS with dba privileges.

Step 3: Connect to the pdbts_admin user.

Now, let us go ahead and connect to the pdbts_admin user using SQL developer. Open SQL developer and use “New connection”. We are going to use the connection name as pdbts. The username is given as pdbts_admin and the password is given as “goodluck” as mentioned in the creation of the pluggable database, the hostname is oracletest.com and the port number is 1521. The service name is mentioned as “pdbts.com”. So, we have given all these details and checked the connection of the user to the database.

The connection status is a success. So, pdbts_admin can connect to the database. Let’s check the username and database name.

So, we are good with the connection of PDBTS. Now, let’s try to check the connection using the easy connect method.

Command: connect pdbts_admin/goodluck@pdbts

We are getting an error. This is because we haven’t mentioned the entry name in the tnsnames.ora file. Let’s go ahead and make an entry in the tnsnames.ora file. We have mentioned them as pdbts in the connection. So, now let’s go ahead and go to the below location and open the tnsnames.ora file.

Let’s go ahead and open the tnsnames.ora file.

So, let’s take any entry and copy that entry and modify the changes according to the new pluggable database PDBTS.

So, we have made the changes to the tnsnames.ora file according to the new pluggable database. Once this is done let’s try to check the connection again.

Command: connect pdbts_admin/goodluck@pdbts

The connection is successful.

Note: Make sure to enter the password according to the case that you mentioned during the database creation. We have created the pluggable database and we have to use this pluggable database to work on the tablespaces.

In the next article, I am going to discuss Helpful Queries for Tablespace in Oracle. Here, in this article, I try to explain How to Create a new PDB for Tablespace Practice in Oracle. I hope you enjoy Creating a new PDB for Tablespace Practice in the Oracle article.