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

This article mainly explains the "Oracle import and export data when the common mistakes", the article explained the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Oracle import and export data when the common mistakes" bar!

1. Exp export prompt "PLS-00302: SET_NO_OUTLINES components must be described"

If the client is Oracle 11g and the server is Oracle 9i, executing exp will have the following error:

Export: Release 11.1.0.6.0-Production on Friday July 15 21:40:58 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved

EXP-00056: encountered ORACLE error 6550

ORA-06550: line 1, column 41:

PLS-00302: the 'SET_NO_OUTLINES' component must be specified

ORA-06550: line 1, column 15:

PL/SQL: Statement ignored

EXP-00000: export termination failed

There are some problems with the import and export of different versions of Oracle, such as dmp files exp from Oracle 9i (using the exp command on machines with oracle 9i), you can imp to Oracle 11g (machines with Oracle 11g), but not vice versa.

At the same time, if you cannot directly use the exp command on the client side (with Oracle 11g installed) to send a request to the database on the server side (Oracle 9i) to export data to the client machine, it will prompt "PLS-00302".

This is called backward compatibility.

2. Imp imports dmp files with "IMP-00038" error.

Perform imp import operation, prompt

IMP-00038: Could not convert to environment character set's handle

IMP-00000: Import terminated unsuccessfully

This is because the file export uses expdp, while the import uses imp, so the export import format is different.

3. Execute the imp import prompt "IMP-00003"

Imp Import Tip

IMP-00017: following statement failed with oracle error 2298:

"ALTER TABLE" A "ENABLE CONSTRAINT REFEREN"REF_A"

IMP-00003: ORACLE error 2298 encountered

ORA-02298: cannot validate (REFERENCE_O)-parent keys not found

IMP-00017: following statement failed with ORACLE error 2298:

Import terminated successfully with warnings.

The reason may be that a table has been exported at the time of export, but there is a data change in his foreign key association, which leads to inconsistency, so an error is reported.

For the root cause, exp cannot guarantee the integrity of the transaction, such as exporting the main table, then adding records to the main table, and then adding records to the child table and committing it.

The solution is to add the parameter consistent=y to the export instruction, which defaults to no, and its purpose is to maintain a consistent role. For example,

$exp system/oracle file=/home/oracle/exp.dmp owner=oracle consistent=y

If you do not use consistent=y, the import has been completed, and there is data that violates the constraint, so you need to create the constraint. You can use the novalidate clause, or find the irregular data, clear it and then recreate the constraint.

4. Execute the imp import prompt "IMP-00013"

Perform imp import operation, prompt

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

The reason is that the account of the DBA role is used when exp is exported, so the imported account needs to contain the DBA role. But it is generally possible that to import an account is an ordinary account, there is no DBA role, and it is impossible to be granted temporarily, which is more laborious.

Possible solutions:

1. Export the user, perform the temporary revoke DBA role before export, and grant the DBA role after the export is completed.

two。 Grant the role of the import user DBA.

3. Grant the role of the import user imp_full_database.

Each scheme may be suitable for different scenarios. For example, plan 1, can be regarded as a temporary scheme, but it does not look very professional, for some test libraries, the requirements are not high, you can be more casual. Scenario 2, which allows the imported user to have some high-level permission to do so just to import the data, actually increases some risk. Scenario 3, the DBA role is not granted, but in some environments, it may not be allowed to grant roles at will.

Thank you for your reading, the above is the "Oracle import and export data when common errors" of the content, after the study of this article, I believe you on the Oracle import and export data when the common errors of this problem has a deeper understanding, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.