--Check current container
SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
--Switch to a different container
ALTER SESSION SET CONTAINER = salespdb;
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER SESSION SET CONTAINER = PDB$SEED;
--Execute one command in many PDBs at the same time
DECLARE
c1 INTEGER;
rowcount INTEGER;
taskList VARCHAR2(32767) :=
'DECLARE
PRAGMA AUTONOMOUS TRANSACTION;
BEGIN
-- Create the hr.identact table.
EXECUTE IMMEDIATE
''CREATE TABLE hr.identact
(actionno NUMBER(4) NOT NULL,
action VARCHAR2 (10))'';
EXECUTE IMMEDIATE
''INSERT INTO identact VALUES(1, 'ACTION1')'';
-- A commit is required if the tasks include DML.
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- If there are errors, then drop the table.
BEGIN
EXECUTE IMMEDIATE ''DROP TABLE identact'';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;';
TYPE containerListType IS TABLE OF VARCHAR2(128) INDEX BY PLS_INTEGER;
containerList containerListType;
BEGIN
containerList(1) := 'PDB1';
containerList(2) := 'PDB2';
c1 := DBMS_SQL.OPENCURSOR;
FOR conIndex IN containerList.first..containerList.last LOOP
DBMS_OUTPUT.PUT_LINE('Creating in container: ' || containerList(conIndex));
DBMS_SQL.PARSE(c => c1 ,
statement => taskList,
language_flag => DBMS_SQL.NATIVE,
edition= > NULL,
apply_crossedition_trigger => NULL,
fire_apply_trigger => NULL,
schema => 'HR',
container => containerList(conIndex));
rowcount := DBMS_SQL.EXECUTE(c=>c1);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c=>c1);
END;
/
--Change open mode of PDBs databases
ALTER PLUGGABLE DATABASE salespdb, hrpdb OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb CLOSE IMMEDIATE;
--Open a PDB in read write mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN
--Open a PDB in restricted mode
STARTUP PLUGGABLE DATABASE hrpdb RESTRICT
--Open a PDB in restricted and read only mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT
--Open a PDB in read only mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY
--If the PDB is already open, use FORCE to close and open in read write mode
STARTUP PLUGGABLE DATABASE hrpdb FORCE
--Alter system parameters in all containers
ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;
--Execute DML/DDL in a current container
CREATE USER testpdb IDENTIFIED BY password
DEFAULT TABLESPACE pdb1_tbs
QUOTA UNLIMITED ON pdb1_tbs
CONTAINER = CURRENT;
--Create a COMMON user in a CDB (user in all PDBS). Common user MUST start with c##
CREATE USER c##testcdb IDENTIFIED BY password
DEFAULT TABLESPACE cdb_tbs
QUOTA UNLIMITED ON cdb_tbs
CONTAINER = ALL;
--Run catblock.sql script in all containers at once
perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql
--Run catblock.sql script in specif CDBs
perl catcon.pl -u SYS -U SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -c 'HRPDB SALESPDB' -b catblock_output catblock.sql
--Run catblock script in a CDB except from PDBs hrpdb and salespdb
perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -C 'HRPDB SALESPDB' -b catblock_output catblock.sql
--Close a PDB
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
--Change open mode of a PDB to read only
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
--Bring a datafile of a PDB online
ALTER PLUGGABLE DATABASE DATAFILE '/u03/oracle/pdb1_01.dbf' ONLINE;
--Change the default tablespace of a PDB
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;
--Change global database name for a PDB
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO salespdb.example.com;
--Create a service for a PDB using SRVCTL
srvctl add service -db mycdb -service salesrep -pdb salespdb
--Create a service for a PDB using DBMS_SERVICE
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'salesrep',
network_name => 'salesrep.example.com');
END;
/
--General queries
SELECT CDB FROM V$DATABASE;
COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
--Show tables owned by specif schemas in Multiples PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND
t.OWNER IN('HR','OE') AND
p.PDB_ID = t.CON_ID
ORDER BY p.PDB_ID;
--Show users in multiple PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
--Shoe datafiles for each PDBs
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
--Shoe tempfiles in CDB
COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
--Show services associated with a PDBs
COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
ORDER BY PDB;
--Show current container ID and name
SHOW CON_ID
SHOW CON_NAME
SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;
SELECT CON_DBID_TO_ID(2226957846) FROM DUAL;
--History of PDBs
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
--Check open mode of all PDBs
SELECT NAME,OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;
