How to export Schema from Pluggable (PDB) using expdp utility in Oracle 18c
Posted by Mir Sayeed Hassan on June 23rd, 2019
How to export Schema from Pluggable (PDB) using expdp utility in Oracle 18c
Check the database status
SQL> select INSTANCE_NAME, STATUS, VERSION from V$instance; INSTANCE_NAME STATUS VERSION ------------------------------------------ ora18c OPEN 18.0.0.0.0
Check the number of PDB’S exist in CDB
[oracle@oracle18cdb ~]$ !sq sqlplus / as sysdba
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------------------------------------------------------------------------------------------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB_TEST READ WRITE NO
Create a directory at OS Level:
[oracle@oracle18cdb backup]$ mkdir pdb_dump
Create a directory at database level under PDB
SQL> alter session set container=pdb_test; Session altered.
SQL> create directory pdb_dump as '/u01/app/backup/pdb_dump'; Directory created.
SQL> grant read,write on directory pdb_dump to system; Grant succeeded.
[oracle@oracle18cdb ~]$ expdp system/test@pdb_test directory=pdb_dump dumpfile=_23june19.dmp logfile=mir_23june19.log schemas=mir Export: Release 18.0.0.0.0 - Production on Sun Jun 23 01:40:54 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/********@pdb_test directory=pdb_dump dumpfile=mir_23june19.dmp logfile=mir_23june19.log schemas=mir Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . exported "MIR"."agreement" 8.812 KB 17 rows . . exported "MIR"."organization" 8.5 KB 28 rows . . exported "MIR"."sharing_portion" 8.351 KB 2 rows . . exported "MIR"."migrations" 6.531 KB 12 rows . . exported "MIR"."merchant" 6.585 KB 9 rows . . exported "MIR"."broker" 5.976 KB 1 rows . . exported "MIR"."TEST1" 5.101 KB 1 rows . . exported "MIR"."card" 0 KB 0 rows . . exported "MIR"."shareholder" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is: /u01/app/backup/pdb_dump/mir_23june19.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jun 23 01:41:47 2019 elapsed 0 00:00:52