Useful Commands for managed Oracle 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 status and startup time of Pluggable database

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

Start and Stop the pluggable database:

Easily control the pluggable database’s operation by starting and stopping it as needed.

alter pluggable database PDB1 open;
alter pluggable database PDB$SEED CLOSE IMMEDIATE;

Delete or Drop the pluggable database permanent

Removing the permanent pluggable database improves performance and scalability by streamlining database interactions, leading to quicker access times and simplified plugin management.

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

Command to 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 service used for PDBs

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
/