Some VERY Useful 12c Commands

--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;