🧩 How to Refresh a Schema from Production to Development Using Data Pump in Oracle Database

 🧩 How to Refresh a Schema from Production to Development Using Data Pump in Oracle Database

Refreshing a schema is one of the most common DBA tasks — especially when developers need up-to-date data from Production in their Development environment. Let’s see how to do it easily using Oracle Data Pump 🚀

💡 Scenario:

You want to export schema HR from the PROD database and import it into the DEV database.

⚙️ Step 1️⃣ – Create a Directory Object (if not already exists)

CREATE DIRECTORY DATAPUMP_DIR AS '/u01/backup';

GRANT READ, WRITE ON DIRECTORY DATAPUMP_DIR TO system;

⚙️ Step 2️⃣ – Export the Schema from Production

Run this on PROD 👇

expdp system/oracle@PROD schemas=HR directory=DATAPUMP_DIR dumpfile=hr_prod.dmp logfile=hr_prod.log

⚙️ Step 3️⃣ – Copy the Dump File to Development Server

Use scp, sftp, or any transfer method 👇

scp /u01/backup/hr_prod.dmp oracle@dev_server:/u01/backup/

⚙️ Step 4️⃣ – Import the Schema into Development

Run this on DEV 👇

impdp system/oracle@DEV schemas=HR directory=DATAPUMP_DIR dumpfile=hr_prod.dmp logfile=hr_imp.log remap_schema=HR:HR_DEV remap_tablespace=USERS:USERS_DEV

⚙️ Step 5️⃣ – Verify the Imported Objects

SELECT username, created FROM dba_users WHERE username='HR_DEV';

✅ Check tables and grants to confirm the schema refresh is successful.

🎯 Tip:

You can exclude large tables or sensitive data using the EXCLUDE or QUERY parameters in Data Pump for faster and safer refreshes.

🔥 Result:

Your development schema is now a clean copy of Production data — ready for testing without affecting real users 👏

✨ Like & Follow my page for more DBA tips.