首页 » ORACLE 9i-23ai » export data from 11g and import into 10G R2 ORA-01455

export data from 11g and import into 10G R2 ORA-01455

  • 2013/08/13
  • ORACLE 9i-23ai
  • 679 views
  • export data from 11g and import into 10G R2 ORA-01455已关闭评论

今天测试部门的同事问从10r2客户端导出11gr2的数据库时遇到了下面的错误怎么办?下面把过程记录一下，相信有不少同学遇到这样的问题.

[oracle@testdb ~]$ exp  jsfy/jsfy@devdb file=jsfy11g.dmp

Export: Release 10.2.0.4.0 - Production on Tue Aug 13 11:16:58 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. . exporting table              NOTICE_INFO_INDEX          6 rows exported
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

MOS Note

Import data into Oracle8i or higher release database.
Matrix 2: Which Export client to use when importing into an Oracle8i 8.1.7
           or a higher release target database
          (always use the Import client of the target database):

+-----------+--------------------------------------------------------------+
|  EXPORT   |                       IMPORT into:                           |
|   from    +--------+--------+--------+--------+--------+-----------------+
|    \/     |  8.1.7 |  9.2.0 | 10.1.0 | 10.2.0 | 11.1.0 | 11.2.0 |        |
+-----------+--------+--------+--------+--------+--------+--------+--------+
|  5.x  1)2)| EXP5x  | EXP5x  | EXP5x  | EXP5x  | EXP5x  | EXP5x  |        |
|  6.x    2)| EXP6x  | EXP6x  | EXP6x  | EXP6x  | EXP6x  | EXP6x  |        | 
|  7.x    3)| EXP7x  | EXP7x  | EXP7x  | EXP7x  | EXP7x  | EXP7x  |        | 
+-----------+--------+--------+--------+--------+--------+--------+--------+
|  8.0.3    | EXP803 | EXP803 | EXP803 | EXP803 | EXP803 | EXP803 |        | 
|  8.0.4    | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 |        | 
|  8.0.5    | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 |        | 
|  8.0.6    | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 |        | 
+-----------+--------+--------+--------+--------+--------+--------+--------+
|  8.1.5    | EXP815 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815 |        | 
|  8.1.6    | EXP816 | EXP816 | EXP816 | EXP816 | EXP816 | EXP816 |        | 
|  8.1.7    | EXP817 | EXP817 | EXP817 | EXP817 | EXP817 | EXP817 |        | 
+-----------+--------+--------+--------+--------+--------+--------+--------+
|  9.0.1    | EXP817 | EXP901 | EXP901 | EXP901 | EXP901 | EXP901 |        | 
|  9.2.0    | EXP817 | EXP920 | EXP920 | EXP920 | EXP920 | EXP920 |        | 
+-----------+--------+--------+--------+--------+--------+--------+--------+
| 10.1.0  4)| EXP817 | EXP920 |                 4)                |        | 
| 10.2.0  4)|   N/S  | EXP920 |                 4)                |        | 
+-----------+--------+--------+--------+--------+--------+--------+--------+
| 11.1.0  4)|   N/S  | EXP920*|        4)       |        5)       |        | 
| 11.2.0  4)|   N/S  | EXP920*|        4)       |        5)       |        |
+-----------+--------+--------+--------+--------+--------+--------+--------+

实际上如果无特殊对象10g exp 是可以导出11g database的,如果你必须使用老版本的客户端且遇到了上面的错误，有时使用 INDEXES=n STATISTICS=none parameters是可以正常导出，原因是当表的统计信息如行数超过了2^31-1的数据限制，导出时用statistics=none或手动清除统计信息的方法解决.

还有一个原因exp 时因为空表(未分配segment)而导致的失败，这是因为11g的新特性叫做deferred segment creation(延迟段创建), 这表空表不会在dba_segments中列出的表.这个新特性受deferred_segment_creation参数的控制，默认值是true,如果设置为false可以禁用了该特性，还有一个方法就是可以手动为空表分配段.

ALTER TABLE   <empty table name>   ALLOCATE     EXTENT;

select ‘alter table ‘|| TABLE_NAME||’ALLOCATE EXTENT;’ from user_tables where SEGMENT_CREATED=’NO’;

TIP:

To have a better performance and more flexibility when exporting from and importing into Oracle10g and higher release databases, we recommend the usage of the Export Data Pump (expdp) and Import Data Pump (impdp) clients

Don’t forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.

Summary
Export/Import compatibility:
1. Export the data with the Export utility of the lowest database version involved.
2. Import the data with the Import utility of the target database.
3. Dump files generated by the Export Data Pump are not compatible with dump files generated by the original Export utility.
4. Exporting with an Oracle7 Export utility from an Oracle9i or higher database is not supported.

回答另一个问题，如何知道导出时的用户名，指写fromuser,给一种方法

[oracle@testdb ~]$ imp system/oracle file=jsfy11g.dmp  fromuser=xxx touser=jsfy

Import: Release 10.2.0.4.0 - Production on Tue Aug 13 11:32:17 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by JSFY, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00034: Warning: FromUser "XXX" not found in export file
Import terminated successfully with warnings.

references:
MOS note.553377.1 and Kirill Loifman ‘s note

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：