In this article we will have a look & hands-on on creating a pluggable database manually with the help of SQL Prompt.
We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter, In following example i am going to create new Pluggable Database, name: ‘newpdb’
[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:
SQL> select global_name from global_name;
GLOBAL_NAME
——————————————————————————–
ORCL
// Create new pluggable database by following set of SQL queries:
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/pdbseed/’,’/u01/app/oracle/oradata/newpdb/’;
Session altered.
SQL> CREATE PLUGGABLE DATABASE newpdb ADMIN USER pdbadmin IDENTIFIED BY Admin123;
Pluggable database created.
// Pluggable database has been created successfully, ensure changes by issuing the following query:
column pdb_name FORMAT A25;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB MOUNTED
OR
column pdb_name FORMAT A25;
SQL>SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
PDB_NAME STATUS
————————- ————-
NEWPDB NEW
PDB$SEED NORMAL
// Above result shows that, newly created pluggable database is in mount state, Pluggable databases automatically created in mount state, Issue the following query to open pluggable database:
SQL> alter pluggable database newpdb open;
Pluggable database altered.
// Ensure the changes by following SQL query, Now newly created pluggable database is in READ WRITE (open) state.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWPDB READ WRITE
Connect your pluggable database with following:
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> alter session set container=newpdb;
Session altered.
SQL> show con_name
CON_NAME
——————————
NEWPDB
You are now connected to your newly created pluggable database. Cheers!!
