oracle-data import and Export-general exp/imp and data pump mode

The "preface" is explained in the blog post:

This article will introduce a personal tone about the Oracle database import and export knowledge, at the current point in time "May 16, 2017 " under the mastery of limited technology, there may be a lot of knowledge understanding is not deep or comprehensive, I hope you point out the issue of common exchange, In the follow-up work and study, if found that the content of this article and the actual situation is biased, will improve the content of this blog post.

The import and export written in this article refers to the import and export of Oracle databases under the command line without the use of tools

< Strong> body:

One: General Exp/imp, import and Export method

1. Export:

command format:exp user/[email protected]/sid file=path/sth.dmp full=y ignore=y;

Actual case:

Exp Mcptest/[email PROTECTED]/CKLP file=/home/oracle/cklptest. DMP full=y ignore=y;

2. Import

command format: imp user/[email protected]/sid filename=fielpath full=y ( full not less ) ignore=y;

Actual case:

Imp mcptest/[email PROTECTED]/CKLP file=/home/oracle/cklptest. DMP full=y ignore=y;

Two: Data pump import and Export method

First, export:

1. Create a default export directory

Note: the operating system level requires this directory to exist, in this case the directory is dmp_dir, corresponding to the operating system level of the/orasoft directory

Sql>create directory Dmp_dir as '/orasoft ';

2. Give the specified user read and write permissions in this directory

Sql>grant Read,write on the DIRECTORY dmp_dir to system;

3. Exporting a database using EXPDP

EXPDP [dba_user/passwd] @SID full=y parallel=2 directory=dmp_dir dumpfile=<oracle_sid>_%u.dmp logfile=log_ Name.log

Second, import:

1. Create a default export directory

Sql>create directory Dmp_dir as '/app/oracle ';

2. Give the specified user read and write permissions in this directory

Sql>grant Read,write on the DIRECTORY dmp_dir to system;

3. Use the IMPDP tool for full-Library import

IMPDP [dba_user/passwd] @SID full=y parallel=2 directory=dmp_dir dumpfile=<oracle_sid>_%u.dmp logfile=log_ Name.log

Three: Real case analysis-data pump export

1. Create a default export directory

Sql>create directory Dump_dir as '/orasoft/dumpfile ';

2. Give the specified user read and write permissions in this directory

Sql>grant Read,write on the DIRECTORY dump_dir to system;

3. Export the entire database "operating system level to execute this command":

# cd/orasoft# EXPDP system/manager full=y cluster=no parallel=2 directory=dump_dir dumpfile=fccklpfull_%u.dmp logfile= Log_name.log

Note: the third operation needs to be performed in the path/orasoft directory , where the server_name is fccklp,dmp called Fccklpfull, which represents the entire library data, and This must be written in the form of a fccklpfull_%u.dmp, because it is not possible to specify a file in 2-way parallel, so the%u parameter is required.

strongly NOTE: The RAC cluster environment does not need to be @sid, that is, just enter [dba_user/passwd], and no need to add @sid

If you export a single user, you need to add an additional parameter:

EXPDP [dba_user/passwd] @SID schemas=[user name] full=y parallel=2 directory=dmp_dir dumpfile=<oracle_sid>_%u.dmp Logfile=log_name.log

Quad: RAC cluster data pump export issue record:

Once in the RAC cluster to perform the data pump export, there have been problems, after the DBA toss 2 hours to fix, by the way to record the resolution process

1, Query Export task find Job_name

SELECT * FROM Dba_datapump_jobs

2. Enter the Sqlplus interface to kill the work

Stop_job---->yes

3. Other related SQL

To delete the data pump default directory:

Delete * from dba_directories where directory_name= "Dmp_dir";

Query data Pump default directory:

SELECT * from Dba_directories;

End:

Thank you for reading and wishing you a rewarding day!

This article is from the "Breeze Month Blog" blog, please be sure to keep this source http://watchmen.blog.51cto.com/6091957/1926565

oracle-data import and Export-general exp/imp and data pump mode