SQL> CREATE DIRECTORY dpump AS 'C:\Dpump';

 

SQL> SELECT directory_path

           FROM all_directories

           WHERE directory_name = 'DPUMP’;

 

SQL> CREATE USER nasir IDENTIFIED BY nasir#1

            DEFAULT TABLESPACE Users

           TEMPORARY TABLESPACE temp

           QUOTA UNLIMITED ON users;

 

SQL> GRANT CREATE SESSION,CREATE TABLE TO nasir;

 

SQL> GRANT READ,WRITE ON DIRECTORY dpump TO scott;

 

SQL> GRANT READ,WRITE ON DIRECTORY dpump TO nasir;

 

Table Export/Import:

H:\>EXPDP scott/abc#1 DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept

 Remap Schema

H:\>IMPDP nasir/nasir#1 DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG TABLES=emp REMAP_SCHEMA=scott:nasir

Export Metadata Only

H:\>EXPDP scott/abc#1 DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept CONTENT=METADATA_ONLY

H:\>IMPDP nasir/nasir#1 DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept CONTENT=METADATA_ONLY REMAP_SCHEMA=scott:nasir

 

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

·         ALL loads any data and metadata contained in the source. This is the default.

·         DATA_ONLY loads only table row data into existing tables; no database objects are created.

·         METADATA_ONLY loads only database object definitions; no table row data is loaded.

 

 Schema Exports/Imports

H:\>EXPDP nasir/nasir#1 DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=SCOTT.LOG SCHEMAS=scott

Remap Schema

H:\>IMPDP nasir/nasir#1 DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=IMPSCOTT.LOG SCHEMAS=scott REMAP_SCHEMA=scott:moon

Remap Tablespace

H:\>IMPDP nasir/nasir#1 DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=IMPSCOTT.LOG SCHEMAS=scott REMAP_TABLESPACE=USER1:USER3 REMAP_TABLESPACE=USER2:USER4

Database Exports/Imports

H:\>EXPDP system/manager DIRECTORY=dpump DUMPFILE=DATABASE.DMP LOGFILE=DATABASE.LOG FULL=Y

 

H:\>IMPDP system/sys DIRECTORY=dpump DUMPFILE=DATABASE.DMP LOGFILE=DATABASE.LOG

 FULL=Y REMAP_SCHEMA=system:system

 

Tablespace Exports/Imports

 

H:\>EXPDP 'sys/sys as sysdba' DIRECTORY= dpump DUMPFILE=TUSERS.DMP LOGFILE=EXTUS.LOG TABLESPACES=USERS

 

H:\>IMPDP 'nasir/nasir#1' DIRECTORY= dpump DUMPFILE=TUSERS.DMP LOGFILE=EXTUSERG TABLESPACES=USERS TABLE_EXISTS_ACTION=REPLACE REMAP_SCHEMA=(scott:nasir moon:nasir rahi:nasir)

 

The TABLE_EXISTS_ACTION parameter for Data Pump impdp provides four options:

1. SKIP is the default: A table is skipped if it already exists.

2. APPEND will append rows if the target table’s geometry is compatible. This is the default when the user specifies CONTENT=DATA_ONLY.

3. TRUNCATE will truncate the table, and then load rows from the source if the geometries are compatible and truncation is possible. For example, it is not possible to truncate a table if it is the target of referential constraints.

4.  REPLACE will drop the existing table, then create and load it from the source.