About Linux Import and export operations using the EXPDP and IMPDP commands with the Oracle database

Description: This import and export use the EXPDP and IMPDP command to operate, these 2 commands all need to operate on the server side

Http://www.cnblogs.com/huacw/p/3888807.html

First, export DMP files from an Oracle database

1. To the Linux Oracle user login host, and then create the export file directory/home/oracle/dump (customizable)
Mkdir-p/home/oracle/dump

2. After you log on as the SYS user of the Oracle database (the login command is Sqlplus sys/xnyqsys167@192.168.100.167:1521/xnsde as SYSDBA), execute the following command

Create directory dpdata1 as '/home/oracle/dump '; #创建导出文件的存放目录
#create or replace directory dpdata1 as '/home/oracle/dump '; #用于修改已创建的目录 (please perform selectively according to the actual situation)

SELECT * from dba_directories where directory_name= ' DPDATA1 '; #查看导出文件的存储位置
Grant Read,write on directory dpdata1 to Syssde; #对用户进行授权


3. To exit the SYS user of Oracle, log on to a Linux Oracle user and execute the following command
EXPDP syssde/xnyq34#$@192.168.100.167:1521/xnsde directory=dpdata1 schemas=syssde dumpfile=syssde.dmp

#排除不需要导出的表

EXPDP syssde/xnyq34#$@192.168.100.167:1521/xnsde directory=dpdata1 schemas=syssde dumpfile=syssde.dmp EXCLUDE=TABLE : \ "in\" (\ ' a4_sys_log\ ', \ ' a4_sys_enter\ ' \) \ "

4. Switch to/home/oracle/dump view exported files

importing exported DMP files to another Oracle database

Log in to the local database where you want to import the DMP file

1. View the storage location of the data files in the current database: select name from V$datafile;

2. Create Tablespace Syssde and SDE

Createtablespace syssde datafile '/u01/app/oradata/a4orcl/syssde.dbf ' size 3000m

Createtablespace sde datafile '/u01/app/oradata/a4orcl/sde.dbf ' size 3000m

Note: Use the address of the data file you queried in step 1th when you created the table space

3. Create user Syssde and SDE

CreateUser syssde identifiedby xnyq34#$ default tablespace syssde;

CreateUser SDE identifiedby sde defaulttablespace sde;

4. Give user Syssde and SDE authorization

GRANTDBA, connect to SYSSDE;

GRANTDBA, connect to SDE;

5. View the address of the local directory_path and place the imported DMP file in the directory, if the Linux and Windows paths are incompatible, you can modify it using the following command

select* from dba_directories where directory_name= ' DPDATA1 '; #查看DPDATA1的路径

#create or replace directory dpdata1 as '/home/oracle/dump '; #用于修改dpdata1的路径 (please perform selectively according to the actual situation)

6. Import DMP files (users and passwords created in previous steps)

IMPDP syssde/xnyq34#$ @ORCL11G directory=dpdata1 dumpfile=syssde.dmp schemas=syssde

#使用sys用户导入时

IMPDP \ sys/' sys123 ' @192.168.100.168:1521/a4orcl as Sysdba\ directory=dpdata1 dumpfile=syssde20170907.dmp Syssde

Some of the data from the SYSSDE user of the #将192.168.100.167 database is imported to the CQSDE user of the 192.168.100.168 Database

IMPDP \ sys/' sys34#$ ' @192.168.100.168:1521/a4orcl as Sysdba\ directory=dpdata1 dumpfile=syssde20170906.dmp SCHEMA=SYSSDE:CQSDE REMAP_TABLESPACE=SYSSDE:CQSDE

PS: When using the EXPDP and IMPDP commands, be careful not to put your directory at the end, or there will be export and import failures.