12c Database cheat sheet

This sheet is work in progress.  Please come back for the update...

select view_name from dab_views where view_name like 'CDB%'

select name, pdb from cdb_services;

show con_name

alter pluggable database all open;

startup nomount

select name, open_mode from v$pdbs;

alter database mount; --> select name, open_mode from v$pdbs; all mount

alter database open; -->PDBs$SEED mount mode

alter pluggable database all open:  all pluggable databases in read/write 

select name, pdb from cdb_services;

connect sys/oracle@pdb1 as sysdba

show con_name

show parameter ddl_lock_timeout

There is a single SPFILE per CDB to store prameters. 

SQL> connect sys/oracle@pdb1 as sysdba
Connected.

Instance PDB changes example:

SQL> show parameter ddl_lock_timeout

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer     0
SQL> alter system set ddl_lock_timeout=10;
System altered.

SQL> show parameter timeout

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer     10

SQL> alter system set sessions=150;
System altered.

select name, value, ispdb_modifiable, con_id from v$system_parameter where ispdb_modifiable='TRUE' ==> there are 174 parameters

select p.con_id, p.name, p.dbid, s.name, s.value, s.ispdb_modifiable

from v$system_parameter s, v$pdbs p

where s.con_id = p.con_id and

s.name = 'sessions';

CREATE Permanent tablespaces in a CDB

SQL> create tablespace cdb_tools datafile '/u01/app/oracle/oradata/cdb1/cdb_tools01.dbf' size 10m;                                 
Tablespace created.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> create tablespace pdb2_tools datafile '/u01/app/oracle/oradata/cdb1/pdb2/pdb2_tools01.dbf' size 5m;
Tablespace created.

Create user in Container ==> has to be inside the container DB

SQL> create user C##_MYID_ALL identified by ali container=ALL;
User created.

Create user in PDB ==> from PDB

SQL> create user MYID_DBA identified by ali container=CURRENT;
User created.