12c database syntaxes

From Ittichai Chammavanijakul's Wiki
Jump to navigation Jump to search
  • Create a pluggable database from the seed PDB.
-- Simple
SQL> create pluggable database pdb12c admin user john identified by doe;

-- More
SQL> create pluggable database pdb12c2
admin user john identified by doe
roles = (DBA)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb12c/pdbseed','/u01/app/oracle/oradata/cdb12c/pdb12c2');   
Pluggable database created.

SQL> SQL> select pdb_name, status from cdb_pdbs; 

PDB_NAME	     STATUS
-------------------- -------------
PDB12C		     NORMAL
PDB$SEED	     NORMAL
PDB12C2 	     NEW

SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB12C			       READ WRITE
PDB12C2 		       MOUNTED

SQL> select name, con_id from v$active_services order by 1;

NAME								     CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND								  1
SYS$USERS								  1
cdb12c.localdomain							  1
cdb12cXDB								  1
pdb12c.localdomain							  3
pdb12c2.localdomain							  4

6 rows selected.


SQL> alter pluggable database pdb12c open;
  • Show con_name and con_id.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1
  • Display all services
SQL> select name, con_id from v$active_services order by 1;

NAME								     CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND								  1
SYS$USERS								  1
cdb12c.localdomain							  1
cdb12cXDB								  1
pdb12c.localdomain							  3


  • Create a user in the pluggable database.
-- Cannot create a user in the container database.
SQL> create user test identified by test;
create user test identified by test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

-- Switch to the pluggable database first.
SQL> alter session set container=pdb12c;
Session altered.

-- Verify
SQL> show con_name
CON_NAME
------------------------------
PDB12C

SQL> show con_id
CON_ID
------------------------------
3

SQL> create user test identified by test;
User created.