oracle exp 加版本号,Oracle 数据迁移（从Oracle11G迁移到更高的版本号Oracle10G低版本号）...

1。数据库状况生产环境是11G，linux系统，測试环境是10G，windows系统，须要从生产环境导出一个用户下全部的数据，导入測试环境中。由于数据量比較小，准备採用EXP和IMP工具来做。2，在生产环境上导出用户数据命令：exp \‘system/syspl as sysdba\‘ owner=PLAS buffer=10240000 FILE=/tmp/PLAS_20141113.DMP运行过程：[[email protected] ~]$  exp \‘system/syspl as sysdba\‘ owner=PLAS buffer=10240000 FILE=/tmp/PLAS_20141113.DMPExport: Release 11.2.0.1.0 - Production on Thu Nov 13 19:32:52 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified users .... exporting pre-schema procedural objects and actions. exporting foreign function library names for user PLAS. exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user PLAS............. exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs. exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statisticsExport terminated successfully without warnings.‘

3。在測试环境导入数据3.1。先备份測试环境的数据命令：exp [email protected]_EX as sysdba‘ owner=PLAS buffer=10240000 FILE="F:\TEST-PLAS_EX-1114.DMP"运行过程：C:\Documents and Settings\Administrator>exp [email protected]_EX as sysdba‘ owner=PLAS buffer=10240000 FILE="F:\TEST-PLAS_EX-1114.DMP"Export: Release 10.2.0.1.0 - Production on 星期四 11月 13 20:25:26 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出指定的用户.... 正在导出 pre-schema 过程对象和操作. 正在导出用户 PLAS 的外部函数库名. 导出 PUBLIC 类型同义词. 正在导出专用类型同义词. 正在导出用户 PLAS 的对象类型定义.......... . 正在导出表                        S_MIDORG导出了         738 行. . 正在导出表                      S_MIDUSERS导出了        2746 行. . 正在导出表                     S_ROOM_COMP导出了           3 行. . 正在导出表                 TOAD_PLAN_TABLE导出了           0 行. . 正在导出表                      T_COREMAIL导出了          82 行. 正在导出同义词. 正在导出视图. 正在导出存储过程. 正在导出运算符. 正在导出引用完整性约束条件. 正在导出触发器. 正在导出索引类型. 正在导出位图, 功能性索引和可扩展索引. 正在导出后期表活动. 正在导出实体化视图. 正在导出快照日志. 正在导出作业队列. 正在导出刷新组和子组. 正在导出维. 正在导出 post-schema 过程对象和操作. 正在导出统计信息成功终止导出, 没有出现警告。3.2，导入数据C:\Documents and Settings\Administrator>IMP [email protected]_TEST as sysdba‘ BUFFER=10240000 FILE=F:\ORACLE_EXP\MP_EX-PLAS-20141114.DMP FROMUSER=PLAS TOUSER=PLAS......即将启用约束条件...成功终止导入, 但出现警告。C:\Documents and Settings\Administrator>4，运行过程中错误记录4.1，linux导出报错[[email protected] ~]$ exp "[email protected]" owner=PLAS buffer=10240000 FILE=/tmp/PLAS_20141113.DMPExport: Release 11.2.0.1.0 - Production on Thu Nov 13 19:25:47 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.EXP-00056: ORACLE error 28009 encounteredORA-28009: connection as SYS should be as SYSDBA or SYSOPERUsername:Password:EXP-00056: ORACLE error 1017 encounteredORA-01017: invalid username/password; logon deniedEXP-00005: all allowable logon attempts failedEXP-00000: Export terminated unsuccessfully导入报错，是由于 linux /unix 转义字符，须要在上面加上转义符号，比方\‘，改动成例如以下命令就可以：exp \‘system/syspl as sysdba\‘ owner=PLAS buffer=10240000 FILE=/tmp/PLAS_20141113.DMP4.2。Windows下导入报错C:\Documents and Settings\Administrator> imp [email protected]_TEST as sysdba‘ BUFFER=10240000 FILE=D:\oracle\imp\PLAS_20141113.DMP FROMUSER=PLAS TOUSER=PLASImport: Release 10.2.0.1.0 - Production on 星期四 11月 13 20:53:02 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsIMP-00010: 不是有效的导出文件, 头部验证失败IMP-00000: 未成功终止导入解决的方法： 參考文章http://www.2cto.com/database/201401/272732.html通过工具将dmp文件头部的“11.20.00“ 改为”10.20.10“就可以。4.3，IMP表已经存在的问题......IMP-00015: 由于对象已存在, 下列语句失败:"CREATE PACKAGE             "sss" sss is""""  -- Author  : ADMINISTRATOR""  -- Created : 2012/3/20 16:14:58""  -- Purpose : sssss"......IMP报已经存在的表不能CREATE的问题。通过PLSQL的界面能够在IMP之前删除用户下所以对象，比方删除表的方式为：选择Users->PLAS->Objects->Tables,选中全部表，右键点击Drop操作就会删除掉当前用户PLAS下全部表。之后再运行IMP命令。

----------------------------------------------------------------------------------------------------------------原作者：黄杉 (mchdba)----------------------------------------------------------------------------------------------------------------

版权声明：本文博客原创文章，博客，未经同意，不得转载。