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
/
