Example of EXP/IMP Export Import database in Oracle

Source: Internet
Author: User
Tags dba import database


View the system's Nls_lang settings before exporting:

Echo $NLS _lang
American_america. zhs16cgb231280


Select Userenv (' language ') from dual;
American_america. Zhs16gbk

The difference between the two values is an error:

Exp-00091:exporting questionable statistics

The cause of the problem:
The user environment variable language set in Linux is different from the environment variable language set in the Oracle database.
Solution:
Set the user environment variable language set and database under Linux the same.

Export Nls_lang=american_america. Zhs16gbk

First, export
There are two ways to use Exp:
1, interactive
Perform exp

Username://user Name
Password://password
Enter array Fetch buffer size:4096 >//Export buffer size
Export FILE:EXPDAT.DMP >//Exporting file name
(2) U (sers), or (3) T (Ables): (2) U >//export unit is user or table
Export Grants (YES/NO): yes >//whether authorization is exported
Export table Data (yes/no): Yes >/
Compress extents (yes/no): yes > No//Is compressed, not compressed here

The beginning of the brush on the back of the export.

2, a command mode

Exp userid=nlcmp/nlcmp@nlcmp file=./20150305nlcmp.dmp Log=./1.txt

If added full=y will also lead to some system tables.

Second, import
Also set the environment variable Nls_lang and export the client's character set.

Export Nls_lang=american_america. Zhs16gbk

Import using IMP command:

1, interactive

Implementation IMP

Username://user Name
Password://password
Import file:expdat.dmp >//Importing file location
Enter Insert buffer Size (minimum is 8192) 30720>//import buffer sizes, default
List contents of import file Only (yes/no): no >/whether only list data not imported, default
Ignore create error due to object existence (yes/no): no >//If object already exists, ignore created error, default
Import Grants (yes/no): yes >//Whether permissions are imported, default
Import table Data (yes/no): yes >//Whether or not to import tables, default
Import entire export file (yes/no): no >//whether to import the entire file, select Yes

Then the brush starts to import the data.

2, a command mode

Imp USERID=WANGYI/WANGYI@ORCL full=y file=/tmp/20150305nlcmp.dmp log=/tmp/imp.txt

3. If prompted when importing
1)

Imp-00013:only A DBA can import a file exported by another DBA
Imp-00000:import terminated unsuccessfully

You need to give Imp_full_database permissions:

Grant Imp_full_database to Wangyi;

2)

Warning:the objects were exported by nlcmp

Indicates that the exported user is inconsistent with the imported user and may have different user rights, so there is a warning message, the best user will be the same.

3)

Import done in ZHS16GBK character set and Al16utf16 NCHAR character set
Export server uses UTF8 NCHAR character set (possible ncharset conversion)

is because the export-side national character set is UTF8, the import-end national character set is AL16UTF16, not the same, Oracle will do its own conversion.

III. Import and Export principles

1. When exporting, Oracle converts data from the database character set to the client character set, and Oracle makes data conversions if they are not the same. Therefore, exp checks that the export database character set and the client character set are consistent and that there is a warning message when exporting.
2. At the time of import, Oracle converts the character set of the imported data into the import client character set, and then converts it to the database character set when it is stored in the database.

3. Summary

The rules for exporting and importing are:
Export database character set and import database character set, must be the same or a subset and superset of the relationship.
The export client character set and the import client character set are enforced to match the current database character set.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.