Oracle Exp/imp Sample and usage issues collation

Oracle commonly used migration tools, such as Exp,imp,expdp,impdp,dblink, the actual process can be based on the characteristics of the tool, the specific environment of the database, flexible adoption.
Exp/imp tools should be easy to use, in the case of small amounts of data used very frequently, this brief record of the problems encountered in the use.

First, the Exp/imp version can not be compatible with the problem, the database version of the problem must be clear.

1. The lower version of Exp/imp can be connected to a higher version of the database, and the higher version Exp/imp cannot connect to the lower version of the database.
2. The high version exp exported DMP file, the low version Imp command could not be imported.
3. The low version exp exported DMP file, the High version Imp command can be imported.

Attention:
The operator must have sufficient permissions, insufficient permissions, and it will prompt you to create the appropriate user and table space before importing.

Two, IMP and exp use different character Set 1. Querying the database character set

Sql> SELECT * from v$nls_parameters where parameter= ' nls_characterset ';

PARAMETER VALUE
------------------------------- ----------
Nls_characterset ZHS16GBK

Sql> quit

2. Specify that the client character set is consistent with the database

$ export Nls_lang=american_america. Zhs16gbk

Windows system under the Cmd:set Nls_lang=american_america. Zhs16gbk

Otherwise, the following error will appear when exporting: exp-00091:exporting questionable statistics.

Three, the whole library export, single user import problem 1. Full Library Export

$ exp system/oracle file=system.dmp direct=y full=y;

Direct=y Direct Path Reading
recordlength=65535 Buffer

2. By user import to specify the buffer parameter, otherwise error IMP-00032 and IMP-00008.

$ imp abc/abc file=system.dmp fromuser=abc touser=abc buffer=5400000;

3. Export all object data for the user by user

$ exp ABC/ABC file=abc.dmp direct=y log=abc.log

Note: To have the habit of logging, you will see the contents of the error, for processing.

Four, example: 1. Export all objects under the Hr,tom user

Exp hr/hr file=hr_tom.emp Owner=hr,tom log=hr_tom.log

2. Import HR Objects

Imp hr/hr file=hr_tom.emp fromuser=hr touser=hr

3. Import data Extraction Speed Example

The buffer parameter value reduces the number of disk reads, ignore=y the tables that exist at the time the table was imported, and directly incrementally imports the data. If there is no consistency constraint, the copied data will be imported and feedback will show the progress.

Imp abc/abc file=abc.dmp buffer=10240000 commit=y feedback=100000 ignore=y

4. Table Space Transfer

Oracle Transport Table Space Migration database
http://koumm.blog.51cto.com/703525/1574822

5. Prompt the SQL file directly from the DMP file

1) Prompt the SQL file from the DMP file
Imp abc/abc file=abc.dmp fromuser=abc touser=abc indexfile=abc.sql

2) Table creation involving foreign KEY constraints will not succeed, this should be separated from the primary key foreign KEY constraint, you can extract the build table script:
Imp abc/abc file=abc.dmp fromuser=abc touser=abc indexes=n constraints=n indexfile=abc.sql

EXP option:

Buffer: The download data buffer, in bytes, is dependent on the operating system by default
Consistent: The data involved in the download remains read only, and the default is n
Direct: Using pass-through mode, default = N
Feeback: Displays the number of processing records, the default is 0, that is, does not display
File: output files, default = Expdat.dmp
FileSize: Output file size, defaults to operating system maximum value
Indexes: Whether to download the index, the default is N, which refers to the definition of the index rather than the data, exp does not load the index data
Log:log file, default to None, in standard output display
Owner: Indicates the downloaded user name
Query: Select a subset of records
Rows: Whether to download table records
Tables: List of output table names
IMP options:
Buffer: The upload data buffer, in bytes, that is dependent on the operating system by default
Commit: Whether the commit is executed after the record in the upload data buffer is uploaded
Feeback: Displays the number of processing records, the default is 0, that is, does not display
File: Input files, default is Expdat.dmp
FileSize: Input file size, defaults to operating system maximum value
Fromuser: Indicates the source user side
Ignore: Whether object creation error is ignored, n is default, the object has been established before uploading is often a normal phenomenon, so this option is recommended to be set to Y
Indexes: Whether the index is uploaded, the default is N, which is the definition of the index, not the data, and if the index is established at upload time, this option, even if n is not valid, IMP automatically updates the index data
Log:log file, default to None, in standard output display
Rows: Whether to upload table records
Tables: List of input table names
Touser: Specify the intended user side

This article is from the "Koumm Linux Technology blog" blog, be sure to keep this source http://koumm.blog.51cto.com/703525/1580290

Oracle Exp/imp Sample and usage issues collation