Import and export between different oracle versions

Source: Internet
Author: User

The solution for importing and exporting oracle versions between different versions is as follows. In a special case today, you need to use oracle9 data in oracle8, for example, import and export. After a long time, the data exported from oracle9 cannot be imported into oracle8. Later, I learned from the online query that the import and export of different oracle versions are different from those of the same version. Some rules should follow: In this Article, if the versions are inconsistent, rules should be followed for exp and imp data between databases: 1. The imp tool version should be the same as the target database version. 2. The exp tool version should be the same as the one with the lowest version in the source database and target database. Another article says: rule 1: You can Import data from earlier versions of Oracle to later versions of Oracle, but it is limited to adjacent versions of Oracle, such as from Oracle 7 to Oracle 8. For conversions between two non-adjacent versions, such as from Oracle 6 to Oracle 8, data should be first input to the intermediate version-Oracle 7, and then transferred from the intermediate database to a later version. Rule 2: Oracle's Export/Import tool can only connect to a database with a higher or the same Export/Import version. For example, Oracle 8.0.5's Export/Import (version can be connected to the Oracle 8.1.6 database (version; the Export files from the higher version of Export cannot be read by the lower version of Import; import files exported from earlier versions of Export can be read from later versions. These two statements are basically the same. How can this problem be solved? Instance: for example, machine A oracle9 machine B oralce8 if machine B wants to use machine A's data. There are two ways to do this: first, this is relatively simple. The procedure is as follows: connect to machine A through machine B, export the database, and then import it to machine B. If the two machines cannot be connected. You have to use the second method. 1. Run the catexp of the base version on the High Version database. SQL; 2. Use the EXP of the earlier version to export data of the later version; 3. Use the IMP of the earlier version to import the database to the later version; 4. Run the catexp of the later version on the database of the later version. SQL script. But in 9i, the above method cannot solve the problem. If you use earlier versions of EXP/IMP directly, the following error occurs: EXP-00008: ORACLE error % lu encountered ORA-00904: invalid column name, which is already a published BUG that needs to be addressed by Oracle10.0, the BUG number is 2261722. You can go to METALINK to view details about the BUG. BUG is a BUG. We still need to do our work. We should solve the BUG by ourselves before we have Oracle Support. Execute the following SQL statement in Oracle9i to recreate the exu81rls view. Create or replace view exu81rls (objown, objnam, policy, polown, polsch, polfun, mongots, chkopt, enabled, spolicy) AS select u. name, o. name, r. pname, r. pfschma, r. ppname, r. pfname, decode (bitand (r. stmt_type, 1), 0, '', 'select, ') | decode (bitand (r. stmt_type, 2), 0, '', 'insert, ') | decode (bitand (r. stmt_type, 4), 0, '', 'Update, ') | decode (bitand (r. stmt_type, 8), 0, '', 'delete, '), r. check_opt, r. enable_flag, DEC ODE (BITAND (r. stmt_type, 16), 0, 0, 1) from user $ u, obj $ o, rls $ r where u. user # = o. owner # and r. obj # = o. obj # and (uid = 0 or uid = o. owner # or exists (select * from session_roles where role = 'select _ catalog_role')/grant SELECT on sys. exu81rls to public;/when migrating data between major non-adjacent versions (for example, from O r a c l e 6 to O r a c l e 8, import the data to the intermediate version (in this example, O r a c l e 7 ), then, import the updated major version (O r a c l e 8) from the database of this version ). Conclusion: EXP/IMP can be used across versions, but the EXP and IMP versions must be correctly used: 1. the IMP version is always used to match the database version. For example, to import data to 817, the IMP tool 817 is used. 2. the EXP version is always used to match the lowest version of the two databases. For example, if you import data from 9201 to 817, The EXP tool of the 817 version is used.

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: 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.