Site icon TestingDocs.com

How to connect to Pluggable Database (PDB) using SQL*Plus?

admin

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:

 

Commands

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

Error indication:

ERROR:
ORA-01109: database not open

Warning: You are no longer connected to ORACLE.

Connect as SYS

\>sqlplus / as SYSDBA

List the pluggable database instances:

SQL> SHOW pdbs;

On Single Instance database:

Issue the following command to open the database:

SQL> ALTER PLUGGABLE DATABASE orclpdb OPEN;

On multi-tenancy RAC environment:

We need to open on all instances:

SQL> ALTER PLUGGABLE DATABASE orclpdb OPEN instances=all;

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

 

Connect to the database:

SQL> ALTER SESSION SET container=orclpdb;

Session altered.

Now we can 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. Path to the file

ORACLE_HOME\network\admin

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)
    )
  )

 

Create User to connect

Create a user in the pluggable database:

SQL> CREATE USER scott IDENTIFIED BY tiger;

Grant privileges to the user.

SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO scott;

 

 

Connect to database

We can connect to the database using the CONNECT command. For example to connect as scott database user:

CONNECT username/password@pdbdatabase

SQL> CONNECT scott/tiger@orclpdb;
Connected.
SQL>

—

Database Tutorials on this website:

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

More information about Oracle Database:

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

Exit mobile version