Oracle Solutions We believe in delivering tangible results for our customers in a cost-effective manner
Export & Import Process in Oracle 19c Container Database (CDB/PDB)
In this guide, we’ll walk through how to use the Data Pump (expdp/impdp) utility for export and import when working with Oracle 19c Container Databases (CDBs).
Environment Details
Source (PROD) and Target (TEST) databases are both Oracle 19c.
Operating System: Oracle Enterprise Linux (OEL) 7.9.
A. Preparation Steps for Export (PROD) and Import (TEST)
1. Check Schema Size
SELECT (SUM(bytes)/1024/1024) AS total_size_MB FROM dba_segments WHERE owner = 'BILLING';
2. Verify Directory and Disk Space (on both servers)
On PROD:
On TEST:
ALTER SESSION SET container = TEST;
SELECT * FROM dba_directories;
CREATE DIRECTORY EXP_DIR AS '/u01/export/';
GRANT exp_full_database TO BILLING;
GRANT read, write ON DIRECTORY EXP_DIR TO BILLING;
SELECT DISTINCT(TABLESPACE_NAME) FROM dba_extents WHERE owner = 'BILLING';B. Export Process on PROD Server
1. Run Export
nohup expdp BILLING/xxxx@PROD SCHEMAS=billing DIRECTORY=EXP_DIR DUMPFILE=billing_28042025_%U.dmp PARALLEL=6 LOGFILE=billing_28042025.log COMPRESSION=ALL EXCLUDE=STATISTICS &
2. Monitor Export Progress
SELECT B.USERNAME, A.SID, B.OPNAME, B.TARGET, ROUND(B.SOFAR*100/B.TOTALWORK,0) || '%' AS "%DONE", B.TIME_REMAINING, TO_CHAR(B.START_TIME,'YYYY/MM/DD HH24:MI:SS') START_TIME FROM V$SESSION_LONGOPS B, V$SESSION A WHERE A.SID = B.SID AND B.OPNAME LIKE '%EXPORT%' ORDER BY 6;
3. Copy Dump Files to TEST Server
C. Pre-Import Cleanup on TEST Server
1. Backup Schema DDL (if needed)
2. Kill Active Sessions for Schema
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',' || '@' || inst_id || ''' IMMEDIATE;' FROM gv$session WHERE schemaname = 'BILLING';
3. Lock the User
4. Drop the User
DROP USER BILLING CASCADE;
D. Import Process on TEST Server
1. Set PDB Environment
export ORACLE_PDB_SID=TEST
2. Run Import
impdp "'/ as sysdba'" DIRECTORY=EXP_DIR DUMPFILE=billing_28042025_%U.dmp PARALLEL=4 LOGFILE=imp_billing_28042025.log
3. Monitor Import Progress
E. Post-Import Statistics Gathering
It is recommended to gather schema statistics after import to optimize performance:
EXEC dbms_stats.gather_schema_stats('BILLING', cascade => TRUE, degree => 4);
