Useful command for Managed Oracle 12c Container Database

Create pluggable database in CDB environment

CREATE PLUGGABLE DATABASE pdb5 admin user carrefour identified by Carrefour1!
FILE_NAME_CONVERT=('C:\APP\ADMINISTRATOR\ORADATA\ORCL\DATAFILE','C:\APP\ADMINISTRATOR\ORADATA\ORCL\DATAFILE\PB5');

Check the pluggable database:

select con_id,db_id ,name,open_time from v$pdbs;

Start and Stop the pluggable database

alter pluggable database PDB1 open;

alter pluggable database PDB$SEED CLOSE IMMEDIATE;

Delete the pluggable database permanent:

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

Open all pluggable database

alter pluggable database all open;

Open all pluggable database in restricted mode

alter pluggable database noncdbp open restricted;

Check the container name in which we are present at session level

show con_id
show con_name

Check the PDB Services

SELECT pdb FROM dba_services;

Check the active service

SELECT NAME, CON_ID FROM v$active_services ORDER BY 1;

Check the container database environment

set echo on
SET LINESIZE 200
SET PAGESIZE 10000
SET SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100
SELECT
'DB_NAME: '  ||sys_context('userenv', 'db_name')||
' / CDB?: '     ||(select cdb from v$database)||
' / AUTH_ID: '  ||sys_context('userenv', 'authenticated_identity')||
' / USER: '     ||sys_context('userenv', 'current_user')||
' / CONTAINER: '||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB')
"DB DETAILS"
FROM DUAL
/

Output:
DB_NAME: ORCL / CDB?: YES / AUTH_ID: WIN-TI0N32M5EO5\Administrator / USER: SYS / Container:CDB$ROOT

Check the Status of Pluggable Databases

SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d
using (GUID)
order by v.create_scn
/

Create pluggable database:

create pluggable database TEST admin user app_admin identified by pwd
file_name_convert = ('/pdbseed/', '/TEST/')
/