Data Pump

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.