The data pump export library is used in the Linux operating system.

The data pump export library is used in the Linux operating system.

1. create the physical directory dp_dir on Linux to store the database. dmp file;

Log on as a root user, switch to an oracle user, and create a physical directory as follows:

[Root @ server36 oracle] # su-oracle

[Oracle @ server36 ~] $ Mkdir/home/oracle/dp_dir

Back up the database file to/home/oracle/dp_dir. Method: You can use WinSCP to remotely log on to the host ,. the DMP file is dragged to the directory, but this is displayed. the DMP file owner is root and Its group is root. Therefore, you need to change the user and group of the file, as shown below:

Switch to the root user:

[Root @ server36/] # cd/home/oracle/dp_dir

[Root @ server36 dp_dir] # ls-l

? Faint. 2489816
-Rw-r -- 1 oracle oinstall 28352512 05-08 EFMIS_23_YANSHI_201505080906.dmp
-Rwxr-xr-x 1 oracle oinstall 29253632 05-11 EFMIS_23_YANSHI_201505110900.dmp
-Rw-r -- 1 root 95219712 05-11 :35 hqoatest_20150511.dmp
-Rw-r -- 1 oracle oinstall 436692 05-08 imp_EFMIS_23_YANSHI_201505080906.log
-Rw-r -- 1 oracle oinstall 107675 05-08 imp_EFMIS_ZY_201505080910.log
-Rw-r -- 1 oracle oinstall 464659 05-11 import. log
[Root @ server36 dp_dir] # chown oracle hqoatest_20150511.dmp/* change the user to which the. dmp file belongs */

[Root @ server36 dp_dir] # ls-l

-Rw-r -- 1 oracle root 95219712 05-11 hqoatest_20150511.dmp

[Root @ server36 dp_dir] # chgrp oinstall hqoatest_20150511.dmp/* change the user group to which the. dmp file belongs */

[Root @ server36 dp_dir] # ls-l

-Rw-r -- 1 oracle oinstall 95219712 05-11 hqoatest_20150511.dmp

[Root @ server36 dp_dir] # chmod + x hqoatest_20150511.dmp/* Add executable permissions to the. dmp file */

[Root @ server36 dp_dir] # ls-l

-Rwxr-xr-x 1 oracle oinstall 95219712 05-11 hqoatest_20150511.dmp

 

2. Create the database logical directory dp_dir

Database directories can only be created by sys. Normal users must be authorized to use them;

Assume that the dyl user is the Import and Export user;

[Root @ server36 dp_dir] # su-oracle
[Oracle @ server36 ~] $ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Mon May 11 17:34:11 2015

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dba_directories;/* query the logical directory */

SQL> create directory dp_dir as '/home/oracle/dp_dir';/* create a database logical directory */

Directory created.

 

3. Create a new user and attach the executable. dmp File Permission to the new user;

SQL> create user dyl identified by 1;/* create a new user with a password of 1 */

User created.

SQL> grant read, write on directory dp_dir to dyl;/* grant the user dyl the readable and executable permission on the directory dp_dir. Attach the permission as system */

Grant succeeded.

4. Data Pump export data
1) by User Guide
Expdp system/manager schemas = scott DIRECTORY = dpdata dumpfile = expdp. dmp;
2) parallel process parallel
Expdp system/manager directory = dpdata dumpfile = scott3.dmp parallel = 40 job_name = scott3

3) import by table name
Expdp system/manager TABLES = emp, dept directory = dpdata dumpfile = expdp. dmp;
4) export by query Conditions
Expdp system/manager directory = dpdata dumpfile = expdp. dmp Tables = emp query = 'where deptno = 20 ';
5) export by tablespace
Expdp system/manager DIRECTORY = dpdata DUMPFILE = tablespace. dmp TABLESPACES = temp, example;
6) import the entire database
Expdp system/manager DIRECTORY = dpdata DUMPFILE = full. dmp FULL = y;

5. Restore data
1) Export to a specified user
Impdp system/manager DIRECTORY = dpdata DUMPFILE = expdp. dmp SCHEMAS = scott;
2) Change the table owner.
Impdp system/manager DIRECTORY = dpdata DUMPFILE = expdp. dmp TABLES = scott. dept REMAP_SCHEMA = scott: system;
3) Import tablespace
Impdp system/manager DIRECTORY = dpdata DUMPFILE = tablespace. dmp TABLESPACES = example;
4) import the database
Impdb system/manager DIRECTORY = dpdata DUMPFILE = full. dmp FULL = y;

5) append data
Impdp system/manager DIRECTORY = dpdata DUMPFILE = expdp. dmp SCHEMAS = system TABLE_EXISTS_ACTION = append;
6) Parallel commands
Export the scap user from oss_scap_83 (db_link name) and import it to the scap user on the local database.
Impdp system/oracle NETWORK_LINK = oss_scap_83 directory = dpdata SCHEMAS = scap job_name = expdmp parallel = 4

Appendix:

Import operations in different tablespaces of different users:

Impdp user name/password directory = 'name of the directory you created 'dumpfile = 'name of the exported file'

Remap_schema = Name of the exported User: name of the user to be imported remap_tablespace = Name of the exported tablespace: name of the tablespace to be imported

 

For example:

[Oracle @ server36 ~] $ Impdp system/oracle@XXX.XXX.XX.XXX/service_name directory = dp_dir remap_schema = EFMIS_23_YANSHI: efmis_23_20150511 dumpfile = EFMIS_23_YANSHI_201505110900.dmp

Remap_schema = exported User: user to be imported

Service_name: generally orcl, which can be queried: select instance_name from v $ instance;