Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the considerations for exp and imp data migration in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

First, when exp, the os language environment and the database will be automatically converted at the same time.

If the character set of the operating system is smaller than the character set of the database, garbled may occur. If the source and target library character sets are

Similarly, to prevent character conversion in the exp,imp process, we can manually set the voice environment and database language environment before the exp,imp action. Such as database language environment

AMERICAN_AMERICA.AL32UTF8,os is windows, use the command set NLS_LANG=AMERICAN_AMERICA.AL32UTF8;os is linux or unix, use the command

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8.

Second, when migrating schema, even if the data is not migrated, ORA-1659 errors are easy to occur.

After the above error occurred, select view found that the size of the table is the same as that of the source database table. In fact, we just migrate the table structure, not the data (rows=n).

The query table is found to have no migrated data, but its storage space is the same as the source table. In other words, it migrates storage space. According to the analysis, it is found that the parameter is not set during exp.

Due to the number of compress=n, because the default value of this parameter is y, it will compress all the data of the source table into an extent, that is, initial extent, when exp, and when imp, it will

Pre-allocated initial extent size. Therefore, in this case, ORA-1659 errors are very easy to occur.

Note: in addition, when exporting data, if the table size is large, using the default value of this parameter is also prone to ORA-1659 errors. Because suppose your source table size is 100m, in the

When imp, you need to allocate an initial extent size of 100m. If the user's tablespace does not have a continuous 100m space, then this error will be reported.

Third, if you export statistics, if you export only part of the data or no data, the exported statistics will report an error. In addition, if the statistics are not exported, but when importing, you need to import the statistics

At this point, after import, the statistics are locked and cannot be updated.

At this point, we can use the package dbms_stats.unlock_schema_stats to unlock it. The best way is to add the parameter statistics=none instead of exp,imp in exp,imp.

Statistics, after the import is completed, re-collect statistics.

Fourth, when using ftp cross-system cp dmp files, we must pay attention to the use of bin mode to prevent imp, the file can not open, and import failure.

Fifth, in order to prevent ORA-1555 errors in imp data operations, especially in imp large tables, the parameter commit=y,buffer=10000000 should be set (set according to the actual situation). At this time, when the buffer space is full, it will be submitted automatically. The default is imp to submit a table once.

Detailed examples

Migrate users, but there is no need to migrate data, and both the source database and target database locales are AMERICAN_AMERICA.AL32UTF8. You can do the following:

Export:

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Exp user/pwd file=export.dmp log=export.log buffer=10000000 grants=n rows=n statistics=none compress=n

Import:

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Imp user/pwd fromuser=user1 touser=user2 rows=n statistics=none file=export.dmp log=export.log

Thank you for reading! This is the end of this article on "what are the points for attention in exp and imp data migration in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.