DT's Blog
0%

Oracle_PDB_and_CDB

Posted on In

Pretty Sqlplus Output

1
2
3
4
5
6
set lines 256;
set trimout on;
set tab off;
set pagesize 100;
set colsep " | ";
COLUMN column_name FORMAT model;

switch PDB

1
alter session set container = PDB_NAME;

Conn to CDB from PDB

1
ALTER SESSION SET CONTAINER=cdb$root;

Open PDB

1
2
alter pluggable database PDB_NAME open;
alter pluggable database all open;

Auto Open PDB when CDB restart

  1. Create trigger at CDB
    1
    2
    3
    4
    5
    6
    CREATE TRIGGER open_all_pdbs
      AFTER STARTUP ON DATABASE
    BEGIN
       EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
    END ;
    /

    Create unlimited bigfile tablespace

    1
    CREATE BIGFILE TABLESPACE "NSNPR_DATA" DATAFILE 'D:/datafiles/NSNPR/NSNPR_tvc_index.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 3G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

    Create user and grant privilege

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    create user NSNPR IDENTIFIED BY XXXXX
      default tablespace NSNPR_DATA
      temporary tablespace TEMP
      profile DEFAULT;
    -- Grant/Revoke role privileges 
    grant connect to NSNPR;
    grant resource to NSNPR;
    -- Grant/Revoke system privileges 
    grant create database link to NSNPR;
    grant create public database link to NSNPR;
    grant create table to NSNPR;
    grant create view to NSNPR;
    grant query rewrite to NSNPR;
    grant select any dictionary to NSNPR;
    grant select any table to NSNPR;
    grant unlimited tablespace to NSNPR;
    grant connect to NSNPR;
    grant resource to NSNPR;
    grant DATAPUMP_IMP_FULL_DATABASE to NSNPR;
    grant dba to NSNPR;
    grant create any index to NSNPR;

Create Directory

1
2
create or replace directory EXPDP_1
 as 'D:\dump';

Impdp with multi dump file

1
impdp IMP_User/Pass@pdborcl directory=expdp_1 dumpfile=expdp_nsnpr_DB_15092020_01.dmp,expdp_nsnpr_DB_15092020_02.dmp,expdp_nsnpr_DB_15092020_03.dmp,	expdp_nsnpr_DB_15092020_04.dmp,expdp_nsnpr_DB_15092020_05.dmp,expdp_nsnpr_DB_15092020_06.dmp,expdp_nsnpr_DB_15092020_07.dmp,expdp_nsnpr_DB_15092020_08.dmp,expdp_nsnpr_DB_15092020_09.dmp,expdp_nsnpr_DB_15092020_10.dmp,expdp_nsnpr_DB_15092020_11.dmp,expdp_nsnpr_DB_15092020_12.dmp,expdp_nsnpr_DB_15092020_13.dmp full=y table_exists_action=replace logfile=NSNPR-import-%date:~4,2%-%date:~7,2%-%date:~10,4%.log LOGTIME=ALL METRICS=Y version=12.2

Resize tablespace

1
alter tablespace TABLESPACE_NAME resize 100G;