Oracle Data Pump Examples


Oracle Data Pump Examples

To work on Datapump, we need to unlock the SCOTT account and create a logical directory to access.
The logical directory is a pointer to a physical directory

 

Oracle Data Pump Unlock Scott Schema
===========================
CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

 

Oracle Data Pump Create Physical Directory
==============================

mkdir -p /u01/app/oracle/oradata/dpdir/

 

Oracle Data Pump Create a Logical Directory
==============================

CREATE OR REPLACE DIRECTORY dpdir AS ‘/u01/app/oracle/oradata/dpdir/’;

 

Oracle Data Pump Give Permission on that directory to User
=========================================

GRANT READ, WRITE ON DIRECTORY dpdir TO scott;

GRANT FULL_EXP_DATABASE TO scott;

GRANT FULL_IMP_DATABASE TO scott;

 

Oracle Data Pump Exports/Imports Table
============================

The TABLES parameter indicates that tables are to be exported.

expdp scott/tiger tables=EMP,DEPT directory=dpdir dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log

impdp scott/tiger tables=EMP,DEPT directory=dpdir dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log

 

Oracle Data Pump Exports/Imports Schema
==============================

The SCHEMAS parameter indicates that a complete SCHEMA export.

expdp scott/tiger schemas=SCOTT directory=dpdir dumpfile=SCOTT.dmp logfile=SCOTT.log

impdp scott/tiger schemas=SCOTT directory=dpdir dumpfile=SCOTT.dmp logfile=SCOTT.log

 

Oracle Data Pump Exports/Imports Database
===============================

The FULL parameter indicates that a complete database export.

expdp system/password full=Y directory=dpdir dumpfile=fullexp.dmp logfile=fullexp.log

impdp system/password full=Y directory=dpdir dumpfile=fullexp.dmp logfile=fullexp.log

 

Oracle Data Pump Exports/Imports Estimate
===============================

expdp dpuser/dpuser schemas=DMTRAG ESTIMATE_ONLY=y


Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."