Data Pump Export/Import How we can do it easily?

Data Pump Export


1. Create directory This user should have "create any directory" privilege. (grant create any directory to prod;)

SQL> create directory dexport as '/oradata' ;

(here '/oradata' is the destination where export file will be created.)

2. Invoke Data Pump Export

(i)Full Database Export

SQL>host expdp user_id/pass dumpfile=file_name.dmp directory=dexport full=y

(For Full Database Export, user must have EXP_FULL_DATABASE role) (grant exp_full_database to prod;)

(ii)Schema Export

expdp user_id/pass dumpfile=file_name.dmp directory=dexport schema=your_schema_name

(To export another schema user must have EXP_FULL_DATABASE role)

(iii)Table Export

expdp user_id/pass dumpfile=file_name.dmp directory=dexport tables=(list of tables separated by comma)

(To export another schema's table user must have EXP_FULL_DATABASE role)

(iv)Tablespace Export

expdp user_id/pass dumpfile=file_name.dmp directory=dexport tablespaces=(list of tablespaces)

(Privileged users get all tables. Nonprivileged users get only the tables in their own schemas.)

Data Pump Import
Move the dump file in the computer that you want to invoke import and put it in a location like '/oradata'.

1. Create directory This user should have "create any directory" privilege.(as a sys user execute grant create any directory to prod;)

SQL> create directory dimport as '/oradata'

2. Invoke Data Pump Import (For Full Database Import, user must have IMP_FULL_DATABASE role) (grant imp_full_database to prod;)

SQL> host impdp user_id/pass dumpfile=file_name.dmp directory=dimport full=y

(If you set full=y then everything in dump file will be imported. If you want import a particular object then you can specify either tables/schemas/tablespaces clause. Note that export by full=y and then import by schema=a brings same result as of export by schema=a and then import by full=y)

Related Documents:
Data Pump Export/Import

Export/Import from Enterprise Manager

Original Export/Import