12c database syntaxes
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.