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

主题： 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 > CREATE TABLE MA_LOB_DUMP ( n NUMBER , c CLOB )
   

4.       lob ( c ) STORE AS MA_SEG (
   

5.      TABLESPACE users
   

6.       CHUNK 4096
   

7.       NOCACHE LOGGING
   

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

9. 
   

10. Table created .

2. 向LOB表插入数据

点击( 此处 )折叠或打开

1. SQL > insert into MA_LOB_DUMP values ( 1 , '12' ) ;
   

2. 
   

3. 1 row created .
   

4. 
   

5. SQL > insert into MA_LOB_DUMP values ( 1 , '123' ) ;
   

6. 
   

7. 1 row created .
   

8. 
   

9. SQL > insert into MA_LOB_DUMP values ( 2 , '2345' ) ;
   

10. 
    

11. 1 row created .
    

12. 
    

13. SQL > insert into MA_LOB_DUMP values ( 3 , 'asdsada' ) ;
    

14. 
    

15. 1 row created .
    

16. 
    

17. SQL > commit ;
    

18. 
    

19. Commit complete .

3. 检查LOB表里的数据

点击( 此处 )折叠或打开

1. SQL > select * from MA_LOB_DUMP ;
   

2. 
   

3.      N                        C
   

4. ----------         ----------------- ------ -

5.      1                      12

6.      1                      123

7.      2                      2345

8.      3                      asdsada

4. 查看LOB表的LOB名

点击( 此处 )折叠或打开

1. SQL > select TABLE_NAME , SEGMENT_NAME , TABLESPACE_NAME from user_lobs where table_name = 'MA_LOB_DUMP' ;
   

2. 
   

3. TABLE_NAME           SEGMENT_NAME     TABLESPACE_NAME
   

4. -----------------  -------------  ------------------------------
   

5. MA_LOB_DUMP               MA_SEG             USERS

5. exp工具导出LOB表

点击( 此处 )折叠或打开

1. [ oracle @ rhel - ORCL dump ] $ exp hr / hr file = / home / oracle / dump / ora10gCLOB . dmp tables = MA_LOB_DUMP
   

2. 
   

3. Export : Release 10 . 2 . . 1 . 0 - Production on Wed Jan 24 16 : 30 : 25 2018
   

4. 
   

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

6. 
   

7. 
   

8. Connected to : Oracle Database 10g Enterprise Edition Release 10 . 2 . . 1 . 0 - 64bit Production
   

9. With the Partitioning , OLAP and Data Mining options
   

10. Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    

11. server uses AL32UTF8 character set ( possible charset conversion )
    

12. 
    

13. About to export specified tables via Conventional Path . . .
    

14. . . exporting table MA_LOB_DUMP          4 rows exported
    

15. Export terminated successfully without warnings .
    

16. [ oracle @ rhel - ORCL dump ] $ ls
    

17. ora10gCLOB . dmp
    

18. [ oracle @ rhel - ORCL dump ] $

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

   1. 
      

   2. 
      

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

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

   
   

   1. SQL > create directory oradump as '/home/oracle/dump' ;
      

   2. 
      

   3. Directory created .
      

   4. 
      

   5. SQL > grant read , write on directory oradump to public ;
      

   6. 
      

   7. Grant succeeded .

   8. 
      

   9. SQL > alter user oe identified by "oe" account unlock ;
      

   10. 
       

   11. User altered .
       

   12. 
       

   13. SQL > alter user hr identified by "hr" account unlock ;
       

   14. 
       

   15. User altered .

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

1. 
   

   点击( 此处 )折叠或打开

   1. [ oracle @ rhel - ORCL dump ] $ impdp system / oracle directory = oradump dumpfile = ora10gCLOB . dmp logfile = ora10gCLOB . log REMAP_SCHEMA = hr : oe REMAP_TABLESPACE = users : testlob ignore = y
      

   2. 
      

   3. Import : Release 11 . 2 . . 3 . 0 - Production on Wed Jan 24 16 : 41 : 41 2018
      

   4. 
      

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

   6. 
      

   7. UDI - 28002 : operation generated ORACLE error 28002
      

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

   9. 
      

   10. Connected to : Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - Production
       

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

   12. ORA - 39001 : invalid argument value
       

   13. ORA - 39000 : bad dump file specification
       

   14. ORA - 39143 : dump file "/home/oracle/dump/ora10gCLOB.dmp" may be an original export dump fi

1. 
   

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

1. 
   

1. 8. 使用imp工具导入

1. 
   

   点击( 此处 )折叠或打开

   1. [ oracle @ rhel - ORCL dump ] $ imp hr / hr file = / home / oracle / dump / ora10gCLOB . dmp log = ora10gCLOB . log REMAP_SCHEMA = hr : oe REMAP_TABLESPACE = users : testlob ignore = y
      

   2. LRM - 00101 : unknown parameter name 'REMAP_SCHEMA'
      

   3. 
      

   4. IMP - 00022 : failed to process parameters , type 'IMP HELP=Y' for help
      

   5. IMP - 00000 : Import terminated unsuccessfully
      

   6. 
      

   7. 
      

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

   9. 
      

   10. Import : Release 11 . 2 . . 3 . 0 - Production on Wed Jan 24 16 : 50 : 42 2018
       

   11. 
       

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

   13. 
       

   14. 
       

   15. Connected to : Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - Production
       

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

   17. 
       

   18. Export file created by EXPORT : V10 . 02 . 01 via conventional path
       

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

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

   21. IMP - 00007 : must be a DBA to import objects to another user '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
       

   24. 
       

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

   26. 
       

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

   28. 
       

   29. 
       

   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
       

   34. 
       

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

   36. 
       

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

   38. 
       

   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 objects into OE
       

   42. . . importing table "MA_LOB_DUMP" 4 rows imported
       

   43. Import terminated successfully with warnings .

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

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

   1. SQL > conn oe / oe
      

   2. Connected .
      

   3. SQL > select * from MA_LOB_DUMP ;
      

   4. 
      

   5.      N         C
      

   6. ---------  ----------
      

   7.      1         12
      

   8.      1         123
      

   9.      2         2345
      

   10.      3         asdsada

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

1. 
   

   点击( 此处 )折叠或打开

   
   
   

   1. -- 用户的默认表空间
      

   2. SQL > select USERNAME , DEFAULT_TABLESPACE from user_users ;
      

   3. 
      

   4. USERNAME                          DEFAULT_TABLESPACE
      

   5. ------------------------------ ------------------------------
      

   6. OE                                 TESTLOB
      

   7. 
      

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

   9. 
      

   10. SQL > select TABLE_NAME , TABLESPACE_NAME from user_tables where table_name = 'MA_LOB_DUMP' ;
       

   11. 
       

   12. TABLE_NAME                          TABLESPACE_NAME
       

   13. ------------------------------ ------------------------------
       

   14. MA_LOB_DUMP                            USERS
       

   15. 
       

   16. 
       

   17. -- 移动TABLE到表空间TESTLOB
       

   18. SQL > alter table MA_LOB_DUMP move tablespace TESTLOB ;
       

   19. 
       

   20. Table altered .
       

   21. 
       

   22. SQL > select TABLE_NAME , TABLESPACE_NAME from user_tables where table_name = 'MA_LOB_DUMP' ;
       

   23. 
       

   24. TABLE_NAME                         TABLESPACE_NAME
       

   25. ------------------------------ ------------------------------
       

   26. MA_LOB_DUMP                           TESTLOB
       

   27. 
       

   28. 
       

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

   30. SQL > select TABLE_NAME , TABLESPACE_NAME , SECUREFILE from user_lobs where table_name = 'MA_LOB_DUMP' ;
       

   31. 
       

   32. TABLE_NAME      TABLESPACE_NAME         SEC
       

   33. -------------  ----------------- ------------------------------ ---
       

   34. MA_LOB_DUMP      USERS                   NO

2. 
   

3. 
   

4. 
   

5. 
   

6. 
   

   
   

   
   

7. 
   

8. 
   

9. 
   

10. 
    

11. 
    

12. 
    

13. 
    

14. 
    

15. 
    

16. 
    

17. 
    

18. 
    

19. 
    

20. 
    

21. 
    

22. 
    

23. 
    

24. 
    

25. 
    

26. 
    

27. 
    

28. 
    

29. 
    

30. 
    

31. 
    

32. 
    

33. 
    

34. 
    

35. 
    

36. 
    

37. 
    

38. 
    

39. 
    

40. 
    

41. 
    

42. 
    

43. 
    

44. 
    

45. 
    
    

46. 
    

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

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