How to connect to pluggable database (PDB) using SQL*Plus?

Overview

In this tutorial, we will learn the steps involved to connect to pluggable database (PDB) using SQL*Plus command line tool.

General steps are:

  • Launch SQL*Plus command line.
  • Issue SQL commands.

Commands

Open the PDB if the database is not open. The name of the pdb in this examples is: orclpdb

Error indication:

ERROR:
ORA-01109: database not open

Warning: You are no longer connected to ORACLE.

Issue the following command to open the database:

SQL> ALTER PLUGGABLE DATABASE orclpdb OPEN;

Connect Pluggable Database Oracle

Common Error

We need to have sufficient privileges to execute this command. Error when we execute this command with insufficient privileges. For example, connect as SYSDBA role to rectify this error.

 

SQL> ALTER PLUGGABLE DATABASE orclpdb OPEN;
ALTER PLUGGABLE DATABASE orclpdb OPEN
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

Oracle Error Insufficient

Connect to the database:

SQL> ALTER SESSION SET container=orclpdb;

Session altered.

Now you work with the database like creating local user, database objects etc. Add the information of the pdb to the tnsnames.ora file.

Locate the tnsnames.ora file.

Add the host string to the file.

Save the file.

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

 

Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/