Exp/imp encounter two problems (ORA-00904 and the objects were exported by ABC not by you), ora-00904exported

Exp/imp encounter two problems (ORA-00904 and the objects were exported by ABC not by you), ora-00904exported

Recently, I was responsible for setting up the user environment of a system, which involved importing data from the test environment. due to objective constraints, the table space transfer method cannot be used for synchronization, therefore, the imp/exp or Data Pump method is required. Here imp/exp is used. The following describes the problems encountered during the operation.

Problem 1: ORA-00904 for exp export: "IFREEPOOL": invalid identifierAnd "OLEVEL": invalid identifier Error

First operation:

The version used to execute imp/exp is: 10.2.0.4

The database version used for export is: 10.2.0.3

Execute exp abc/xxx file = test. dmp log = exp_test.log

There are some alarms in the process:

EXP-00008: ORACLE error 904 encountered
ORA-00904: "IFREEPOOL": invalid identifier
...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "OLEVEL": invalid identifier

...

EXP-00000: Export terminated unsuccessfully

This MOS article (726182.1) describes the causes of this problem:

If the source database is 10.2.0.3, the export client must be 10.2.0.3, mainly because the export tool keeps accessing the data dictionary object, such as SYS. EXU9LOG, but a new column "IFREEPOOL" is introduced in 10.2.0.4. This column information is not found in 10.2.0.3, so it cannot be operated.

From this we can see that the problem may be caused by the table operation on the LOB object. workaround uses version 10.2.0.3 and later. Therefore, 10.2.0.1 is used, and the above error is not returned for export.

Question 2: during import, Warning: the objects were exported by ABC not by you didn't import any objects

Execute impxyz/xxx file = test. dmp log = imp_test.log ignore = y commit = y buffer = 300000000 feedback = 10000

Prompt during process:

Warning: the objects were exported by ABC, not by you
...
Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. Importing RTCRM's objects into UAT_RTCRM
Import terminated successfully without warnings.

No object is imported to the target database.

The error message Warning: the objects were exported by RTCRM, not by you indicates that the data to be imported belongs to the ABC user, but the user XYZ is used for import, therefore, you must specify the user name for different import and export usernames. Execute:

Impxyz/xxx file = test. dmp log = imp_test.logfromuser = abc touser = xyzignore = y commit = y buffer = 300000000 feedback = 10000

The import operation is successfully performed.

Imp data import problems

I saw a problem on the internet today.

If imp imports data, can the imported table name be different from the original exported table name, that is, a new table name?

Imp system/system file = mydmp. dmp fromuser = myuser1 touser = myuser2 tables = (mytable1)

In this way, you can import myuser1.mytable1 from the dmp export file to myuser2, but the table name is still mytable1. Can you change it to another table name, such as mytable2?

Thank you for your advice!

You can use synonym to achieve this!

For example, there is a schema: user_a, and there is a table test under user_a.
First exp user_a test table to dmp
Create a table test_new and create table test_new as select * from test_a where 1 = 0 under user_a;
Then, create a synonym under another user (such as system ).
Create synonym test for user_a.test_new;

Next, import the dmp file to the system, and it will naturally go to user_a.test_new. Finally, it will be OK to drop the synonym under the system.

======================================
Sqlplus fan/fan
SQL> create table fan_1 as select * from dba_objects;
Table created.
SQL> create table fan_2 as select * from fan_1 where rownum <1;
Table created.

Exp fan/fan tables = fan_1 file = fan_1.dmp

Sqlplus wrj/wrj
Create or replace fan_1 for fan. fan_2;

Select count (*) from fan_1;
0

$ Imp wrj/wrj file =/oracle/fan_1.dmp commit = y ignore = y full = y
Import: Release 9.2.0.6.0-Production on Thu Aug 28 15:59:56 2008
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0-64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0-Production
Export file created by EXPORT: V09.02.00 via conventional path
Warning: the objects were exported by FAN, not by you
Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. Importing FAN's objects into WRJ
... The remaining full text>
 
How Does oracle Copy a table in Scott to another user?

Export exp:
Exp userid = scott/tiger file = 'd: \ exp. dmp 'Log = 'd: \ exp. log' tables = (EMP, DEPT)

Imp Import
Imp userid = "\" sys/sys as sysdba "\" file = 'd: \ exp. dmp 'Log = 'd: \ imp. log 'tables = (EMP, DEPT) fromuser = scott touser = test

Note:
When importing and exporting data, the command is executed on the operating system, not in the database.
The command does not end with a semicolon ";".
Log parameters are optional.
During import, the userid must have the dba permission. Otherwise, an error is returned:

IMP-00007: must be a DBA to import objects to another user's account

If scott has dba permissions, you can use the following script to import the data:

Imp userid = scott/tiger file = 'd: \ exp. dmp 'Log = 'd: \ imp. log' tables = (EMP, DEPT) fromuser = scott touser = test

Example:
C: \ Documents ents and Settings \ ty> exp userid = scott/tiger file = 'd: \ exp. dmp 'Log = 'd: \ exp. log 'tables = (EMP, DEPT)

Export: Release 10.2.0.1.0-Production on Fri Mar 5 09:31:54 2010

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path...
.. Exporting table EMP 14 rows exported
.. Exporting table DEPT 4 rows exported
Export terminated successfully without warnings.

C: \ Documents ents and Settings \ ty> imp userid = "\" sys/sys as sysdba "\" file = 'd: \ ex ...... the remaining full text>