AI智能
改变未来
当前位置：爱站程序员基地 > 全栈知识 > Oracle > 正文

Oracle导入导出之大字段从10g导出并导入11g

2021-07-02 分类：Oracle 评论(0)

主题：Oracle导入导出之大字段从10g导出并导入11g
作者：基毛飞上天

场景：今天有个客户跟我说：怎么将Oracle 10g的LOB表导出来，并导入到Oracle 11g。为什么无法使用IMPDP工具导入呢？
思路：Oracle 10g 只有EXP/IMP工具。而EXP/IMP的工具都是一套的，所以，在Oracle 11g是不能使用IMPDP工具对EXP导出的DMP文件进行导入。

演示如下：
第一节：先在Oracle10g创建一个LOB表，然后通过EXP工具导出DMP文件。
第二节：把DMP文件传到Oracle11g的主机上，通过IMPDP工具测试导入。
第三节：Oracle11g通过IMP工具成功导入。

—————————-   第一节   —————————-

1. 创建LOB表

点击(此处)折叠或打开

  1. SQL>conn hr/hr

  2. Connected.

  3. SQL>CREATETABLEMA_LOB_DUMP(nNUMBER,cCLOB)

  4.       lob(c)STOREASMA_SEG(

  5.     TABLESPACEusers

  6.       CHUNK 4096

  7.       NOCACHE LOGGING

  8.       STORAGE(MAXEXTENTS5));2    3    4    5    6

  10. Tablecreated.

2. 向LOB表插入数据

点击(此处)折叠或打开

  1. SQL>insertintoMA_LOB_DUMPvalues(1,\’12\’);

  3. 1rowcreated.

  5. SQL>insertintoMA_LOB_DUMPvalues(1,\’123\’);

  7. 1rowcreated.

  9. SQL>insertintoMA_LOB_DUMPvalues(2,\’2345\’);

  11. 1rowcreated.

  13. SQL>insertintoMA_LOB_DUMPvalues(3,\’asdsada\’);

  15. 1rowcreated.

  17. SQL>commit;

  19. Commit complete.

3. 检查LOB表里的数据

点击(此处)折叠或打开

  1. SQL>select*fromMA_LOB_DUMP;

  3.      N                       C

  4. ———-        ————————

  5.      1                      12

  6.      1                      123

  7.      2                      2345

  8.      3                      asdsada

4. 查看LOB表的LOB名

点击(此处)折叠或打开

  1. SQL>selectTABLE_NAME,SEGMENT_NAME,TABLESPACE_NAMEfromuser_lobswheretable_name=\’MA_LOB_DUMP\’;

  3. TABLE_NAME           SEGMENT_NAME     TABLESPACE_NAME

  4. —————–  ————-  ——————————

  5. MA_LOB_DUMP               MA_SEG             USERS

5. exp工具导出LOB表

点击(此处)折叠或打开

  1. [oracle@rhel-ORCLdump]$exphr/hrfile=/home/oracle/dump/ora10gCLOB.dmp tables=MA_LOB_DUMP

  3. Export:Release10.2..1.0-ProductiononWed Jan 24 16:30:25 2018

  5. Copyright(c)1982,2005,Oracle.Allrights reserved.

  8. Connectedto:OracleDatabase10g Enterprise EditionRelease10.2..1.0-64bit Production

  9. Withthe Partitioning,OLAPandDataMining options

  10. Export doneinUS7ASCII charactersetandAL16UTF16 NCHAR characterset

  11. server uses AL32UTF8 characterset(possible charset conversion)

  13. Abouttoexport specified tables via Conventional Path…

  14. ..exportingtableMA_LOB_DUMP          4rowsexported

  15. Export terminated successfully without warnings.

  16. [oracle@rhel-ORCLdump]$ls

  17. ora10gCLOB.dmp

  18. [oracle@rhel-ORCLdump]$

  1. —————————-   第二节   —————————-

  1. 6. 将dmp文件传到Oracle11g的主机上，创建目录对象，并对hr和oe用户解锁

  1. 点击(此处)折叠或打开

    SQL>createdirectoryoradumpas\’/home/oracle/dump\’;

  3. Directorycreated.

  5. SQL>grantread,writeondirectoryoradumptopublic;

  7. Grantsucceeded.

  9. SQL>alteruseroeidentifiedby\”oe\”account unlock;

  11. Useraltered.

  13. SQL>alteruserhridentifiedby\”hr\”account unlock;

  15. Useraltered.

  1. 7. 尝试使用impdp工具导入

  1. 点击(此处)折叠或打开

    [oracle@rhel-ORCLdump]$impdp system/oracledirectory=oradump dumpfile=ora10gCLOB.dmp logfile=ora10gCLOB.logREMAP_SCHEMA=hr:oe REMAP_TABLESPACE=users:testlob ignore=y

  3. Import:Release11.2..3.0-ProductiononWed Jan 24 16:41:41 2018

  5. Copyright(c)1982,2011,Oracleand/orits affiliates.Allrights reserved.

  7. UDI-28002:operation generated ORACLE error 28002

  8. ORA-28002:thepasswordwill expire within 7 days

  10. Connectedto:OracleDatabase11g Enterprise EditionRelease11.2..3.0-Production

  11. Withthe Partitioning,OLAP,DataMiningandRealApplication Testing options

  12. ORA-39001:invalid argumentvalue

  13. ORA-39000:baddumpfilespecification

  14. ORA-39143:dumpfile\”/home/oracle/dump/ora10gCLOB.dmp\”may be an original exportdumpfi

  2. —————————-   第三节   —————————-


  1. 8. 使用imp工具导入

  1. 点击(此处)折叠或打开

    [oracle@rhel-ORCLdump]$imp hr/hrfile=/home/oracle/dump/ora10gCLOB.dmplog=ora10gCLOB.logREMAP_SCHEMA=hr:oe REMAP_TABLESPACE=users:testlob ignore=y

  2. LRM-00101:unknown parameter name\’REMAP_SCHEMA\’

  4. IMP-00022:failedtoprocess parameters,type\’IMP HELP=Y\’forhelp

  5. IMP-00000:Import terminated unsuccessfully

  8. [oracle@rhel-ORCLdump]$imp hr/hrfile=/home/oracle/dump/ora10gCLOB.dmplog=ora10gCLOB.logfromuser=hr touser=oe tablespaces=testlob ignore=y

  10. Import:Release11.2..3.0-ProductiononWed Jan 24 16:50:42 2018

  12. Copyright(c)1982,2011,Oracleand/orits affiliates.Allrights reserved.

  15. Connectedto:OracleDatabase11g Enterprise EditionRelease11.2..3.0-Production

  16. Withthe Partitioning,OLAP,DataMiningandRealApplication Testing options

  18. ExportfilecreatedbyEXPORT:V10.02.01 via conventional path

  19. import doneinUS7ASCII charactersetandAL16UTF16 NCHAR characterset

  20. import server uses AL32UTF8 characterset(possible charset conversion)

  21. IMP-00007:must be a DBAtoimport objectstoanotheruser\’s account

  22. IMP-00000: Import terminated unsuccessfully

  23. [oracle@rhel-ORCL dump]$imp system/oracle file=/home/oracle/dump/ora10gCLOB.dmp log=ora10gCLOB.log fromuser=hr touser=oe tablespaces=testlob ignore=y

  25. Import: Release 11.2.0.3.0 – Production on Wed Jan 24 16:51:16 2018

  27. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  30. IMP-00058: ORACLE error 28002 encountered

  31. ORA-28002: the password will expire within 7 days

  32. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

  33. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  35. Export file created by EXPORT:V10.02.01 via conventional path

  37. Warning: the objects were exported by HR, not by you

  39. import done in US7ASCII character set and AL16UTF16 NCHAR character set

  40. import server uses AL32UTF8 character set (possible charset conversion)

  41. . importing HR\’s objectsintoOE

  42. ..importingtable\”MA_LOB_DUMP\”4rowsimported

  43. Import terminated successfullywithwarnings.

  1. 9.  Oracle11g OE用户检查数据

  1. 点击(此处)折叠或打开

    SQL>conn oe/oe

  2. Connected.

  3. SQL>select*fromMA_LOB_DUMP;

  5.      N         C

  6. ———  ———-

  7.      1         12

  8.      1         123

  9.      2         2345

  10.      3         asdsada

  1. 10. Oracle11g检查LOB段的表空间

  1. 点击(此处)折叠或打开

    — 用户的默认表空间

  2. SQL>selectUSERNAME,DEFAULT_TABLESPACEfromuser_users;

  4. USERNAME                          DEFAULT_TABLESPACE

  5. —————————— ——————————

  6. OE                                 TESTLOB

  8. — TABLE段的默认表空间是USERS

  10. SQL>selectTABLE_NAME,TABLESPACE_NAMEfromuser_tableswheretable_name=\’MA_LOB_DUMP\’;

  12. TABLE_NAME                          TABLESPACE_NAME

  13. —————————— ——————————

  14. MA_LOB_DUMP                            USERS

  17. — 移动TABLE到表空间TESTLOB

  18. SQL>altertableMA_LOB_DUMP movetablespaceTESTLOB;

  20. Tablealtered.

  22. SQL>selectTABLE_NAME,TABLESPACE_NAMEfromuser_tableswheretable_name=\’MA_LOB_DUMP\’;

  24. TABLE_NAME                         TABLESPACE_NAME

  25. —————————— ——————————

  26. MA_LOB_DUMP                           TESTLOB

  29. — LOB段的默认表空间是USERS

  30. SQL>selectTABLE_NAME,TABLESPACE_NAME,SECUREFILEfromuser_lobswheretable_name=\’MA_LOB_DUMP\’;

  32. TABLE_NAME      TABLESPACE_NAME         SEC

  33. ————-  —————– —————————— —

  34. MA_LOB_DUMP      USERS                   NO

    • 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31092650/viewspace-2155768/，如需转载，请注明出处，否则将追究法律责任。

    转载于:http://blog.itpub.net/31092650/viewspace-2155768/

    赞(0) 打赏
    未经允许不得转载：爱站程序员基地 » Oracle导入导出之大字段从10g导出并导入11g
    标签：

    相关推荐

    热门文章

    热门标签

    源码 (2093)Java开发 (1438)Python开发 (1301)vps商家 (1289)夸克网盘 (1270)阿里云盘 (1267)Linux (1189)JavaScript (1119)PHP开发 (1058)计算机网络 (1055)MySQL (1015)Go语言 (985)UI/UE (975)CSS (970)人工智能 (933)Shell (930)安卓 (891)Android开发 (873)C# (861)ASP (815)网站架构 (798)IOS开发 (782)JQuery (759)Oracle (684)网络资源 (575)实用工具 (267)微信 (250)C语言/C++ (245)百度网盘 (208)github (132)