Useful Oracle 12c pluggable database commands

Last updated on March 8th, 2019 at 06:37 pm

Here are very useful Oracle 12c pluggable database commands

Query to check if the database is CDB or non-CDB

SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID
--------------------------- --------- ----------
CDB01 YES 0

Query to check containers in the CDB database. Each PDB and root is a container

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
1 CDB$ROOT READ WRITE 0
2 PDB$SEED READ ONLY 891289600
3 SEED READ WRITE 1248961280
4 TEST READ WRITE  1248961280
5 TEST2 READ WRITE 1248961280

If you are connected to a PDB, it will show that container only

SQL> alter session set container=PDB$SEED;
SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
2 PDB$SEED READ ONLY 891289600

Query to check PDB in the CDB database.

Connected to root container

SQL> alter session set container=CDB$ROOT;

Session altered.

select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
2 5604597646 PDB$SEED READ ONLY
3 823307170 SEED READ WRITE
4 458722011 TEST READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SEED READ WRITE NO
4 TEST READ WRITE NO

Connected to a particular PDB

SQL> alter session set container=SEED;

Session altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs
2 ;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
3 823307170 SEED READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SEED READ WRITE NO
SQL>

Startup and shutdown of PDB

Connected to root container

alter pluggable database test1_tech close;

alter pluggable database test1_tech close immediate;

alter pluggable database test1_tech open;

alter pluggable database test1_tech open read only ;

alter pluggable database test1_tech open force;

If the container is set, we can use below command

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE OPEN FORCE;

ALTER PLUGGABLE DATABASE NOLOGGING;

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

STARTUP OPEN
STARTUP OPEN READ ONLY
STARTUP RESTRICT OPEN READ ONLY

How to add service in CDB

srvctl add service -db CDB01 -service TEST_NEW -pdb TEST
srvctl modify service -db CDB01 -service TEST_NEW -pdb TEST
srvctl remove service -db CDB01 -service TEST_NEW

BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'TEST_NEW',
network_name => 'TEST_NEW.com');
END;
/

BEGIN
DBMS_SERVICE.DELETE_SERVICE(
service_name => 'TEST_NEW');
END;
/

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
-------------------- ----------
CDB01 1
CDB01XDB 1
SYS$BACKGROUND 1
SYS$USERS 1
TEST 4
seed 3

6 rows selected.

Various Show command

show con_name

CON_NAME
------------------------------
CDB$ROOT

show con_id

CON_ID
------------------------------
1

How to close and open all the PDB in single command

alter pluggable database all close immediate;

alter pluggable database all open;

How to drop the pdb

drop pluggable database pdb3_test including datafiles;

Container storage

With root container, we have cdb_tablespaces,cdb_data_files shows the tablespace and datafiles of all the container in the CDB  and dba_tablespaces,dba_data_files will show the root tablespace and datafiles only

alter session set container=CDB$ROOT;

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME CON_ID
------------------------------------------------------------------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1

SYSTEM 2
SYSAUX 2

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------------------------------------ ----------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

When connected to a PDB, cdb_tablespaces,cdb_data_files shows the same information as  dba_tablespaces,dba_data_files for the PDB

 

How to rename the pluggable database

alter pluggable database TEST rename global_name to TEST1;

How to check for Local Undo feature in 12.2

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE

Related Articles

Flashback PDB in Oracle Database 12c Release 2

PDB Relocate in Oracle database 12c Release 2

How to remotely clone a PDB or non-CDB in Oracle Database 12.1

Hot PDB cloning in Oracle database 12.2

Refreshable PDB in Oracle Database 12.2

Leave a Reply