Useful command for Oracle 12c pluggable Database

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/')
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.