Logical backup – export/import and Data Pump | Oracle DBA Tutorial pdf

Logical backup – export/import and Data Pump

Export may be either through conventional path or direct path. In direct path export, the evaluating buffer is bypassed and makes it faster.
Most common export command is (from OS command prompt) – EXP ‘sys/password@mydb as sysdba’
Most common parameters are :
FILE – output file (default expdat.dmp)
DIRECT – direct path (N)
OWNER – list of owners/users
TABLES – list of table names
PARFILE – parameter file name (in this file you can store all options)
TABLESPACES – list of table spaces to export
TRANSPORT_TABLESPACE – export transportable table space metadata (N)
Similarly, import is run as:
IMP ‘sys/password@mydb as sysdba’
FILE – input file (expdat.dmp)
IGNORE – ignore create object errors (N)
ROWS – import data rows (Y)
Example of export,
exp system/password file=F:\Database\exp_mkm.dmp owner=mkm rows=y
You can run export/import in interactive mode i.e. it will ask you about parameters during runtime.
Data pump
From 10g onward, Oracle introduces new utilities EXPDP and IMPDP (as run from OS command prompt) to fast data loading (export/import). As it uses API, it is significantly faster than export/import utility. You can even monitor data pump progress from data dictionary views.
Data pump is run as – IMPDP user/password DIRECTORY=data_pump_dir
DUMPFILE=dpdump.dmp JOB_NAME=my_import
Where, DIRECTORY is an external directory name already created. EXPDP has similar syntax.
Oracle automatically creates data_pump_dir in $ORACLE_HOME/ admin/database_name/ dpdump. However if you want you can create a directory of your own like this –
create directory data_pump_dir as
'C:\Oracle11g\admin\MYDB\dpdump';
You can view location of directory by
select * from dba_directories where DIRECTORY_NAME = 'DATA_PUMP_DIR'
Example:
expdp system/password DIRECTORY=data_pump_dir
DUMPFILE=supermarket.dmp SCHEMAS=mkm
Oracle now advises use of data pump over export/import.