Data pump Import and Export

To use a data pump, you must first create a database directory

Database directory only allows SYS to be created

Normal user must be authorized to use

Suppose Scott User is Export import user

Sql>! mkdir Dp_dir

Sql> Create directory Dp_dir as '/home/oracle/dp_dir ';

Directory created.

Sql> Grant Read,write on directory Dp_dir to Scott;

Grant succeeded.

Data Pump Export method: Export Scott User

Expdp

Export Case 1, export by table

EXPDP scott/tiger directory= dp_dir dumpfile=tab.dmp logfile=scott.log tables=dept,emp

Export Case 2, export by user

EXPDP scott/tiger directory= dp_dir dumpfile=dumpscott.dmp Schemas=scott

Export Case 3, full library export, and parallel export

EXPDP scott/tiger directory= dp_dir dumpfile=full.dmp parallel=4 full=y

Backup files in the/home/oracle/dp_dir/directory

Data Pump Import method: Import Scott User

IMPDP copying files to the/home/oracle/dp_dir/directory

Import Case 1, import by table, from Scott to Scott2

IMPDP scott2/tiger directory=dp_dir dumpfile=tab.dmp tables=scott.dept,scott.emp remap_schema=scott:scott2

Import Case 2, import by user, from Scott to Scott2

IMPDP scott/tiger directory= dp_dir dumpfile=schema.dmp remap_schema=scott:scott2

Import case 3, full library Import

IMPDP scott/tiger directory= dp_dir dumpfile=full.dmp full=y

Report:

Import operations for different users under different table spaces:

IMPDP username/password directory= ' The name of the directory you created ' dumpfile= ' exported file name '

Remap_schema= Exported user name: User name to import remap_tablespace= exported tablespace name: Table space name to import

IMPDP system/[email PROTECTED]/ORCL directory=dp_dir remap_schema=efmis_23_yanshi:efmis_23_20150511 transform=oid:n Dumpfile=efmis_23_yanshi_201505110900.dmp

EXPDP fasp_20000/[email protected]/ORCL directory=dp_dir dumpfile=dumpscott.dmp schemas=fasp_20000

EXPDP efmis_62/[email protected]/ORCL directory=dpdump dumpfile=efmis_62_20150821.dmp schemas=efmis_62

IMPDP system/[email protected]/orcl directory=dp_dir remap_schema=oa_0608:hqoa_20150806 dumpfile= OA_0608_20150806. DMP

Logfile=efmis_62_20150821.log

Data pump Import and Export