有个客户跟我说：怎么将Oracle 10g的LOB表导出来，并导入到Oracle 11g。为什么无法使用IMPDP工具导入呢？那么今天就来演示下！
思路：Oracle 10g 只有EXP/IMP工具，而EXP/IMP的工具都是一套的。所以，在Oracle 11g是不能使用IMPDP工具对EXP导出的DMP文件进行导入。
演示如下：
第1节：先在Oracle10g创建一个LOB表，然后通过EXP工具导出DMP文件。
第2节：把DMP文件传到Oracle11g的主机上，通过IMPDP工具测试导入。
第3节：Oracle11g通过IMP工具成功导入。
1. 创建LOB表
1SQL > conn hr / hr
2Connected .
3SQL > 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
10Table created .
2. 向LOB表插入数据
1SQL > insert into MA_LOB_DUMP values ( 1 , '12' ) ;
21 row created .
3
4SQL > insert into MA_LOB_DUMP values ( 1 , '123' ) ;
51 row created .
6
7SQL > insert into MA_LOB_DUMP values ( 2 , '2345' ) ;
81 row created .
9
10SQL > insert into MA_LOB_DUMP values ( 3 , 'asdsada' ) ;
111 row created .
12
13SQL > commit ;
14Commit complete .
3. 检查LOB表里的数据
1SQL > select * from MA_LOB_DUMP ;
2 N C
3---------- ------------------------
4 1 12
5 1 123
6 2 2345
7 3 asdsada
4. 查看LOB表的LOB名
1SQL > select TABLE_NAME , SEGMENT_NAME , TABLESPACE_NAME from user_lobs where table_name = 'MA_LOB_DUMP' ;
2
3TABLE_NAME SEGMENT_NAME TABLESPACE_NAME
4----------------- ------------- ------------------------------
5MA_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
2Export : Release 10 . 2 . . 1 . 0 - Production on Wed Jan 24 16 : 30 : 25 2018
3Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
4Connected to : Oracle Database 10g Enterprise Edition Release 10 . 2 . . 1 . 0 - 64bit Production
5With the Partitioning , OLAP and Data Mining options
6Export done in US7ASCII character set and AL16UTF16 NCHAR character set
7server uses AL32UTF8 character set ( possible charset conversion )
8About to export specified tables via Conventional Path . . .
9. . exporting table MA_LOB_DUMP 4 rows exported
10Export terminated successfully without warnings .
11[ oracle @ rhel - ORCL dump ] $ ls
12ora10gCLOB . dmp
13[ oracle @ rhel - ORCL dump ] $
6. 将dmp文件传到Oracle11g的主机上，创建目录对象，并对hr和oe用户解锁
1SQL > create directory oradump as '/home/oracle/dump' ;
2Directory created .
3
4SQL > grant read , write on directory oradump to public ;
5Grant succeeded .
6
7SQL > alter user oe identified by "oe" account unlock ;
8User altered .
9
10SQL > alter user hr identified by "hr" account unlock ;
11User altered .
7. 尝试使用impdp工具导入
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
2Import : Release 11 . 2 . . 3 . 0 - Production on Wed Jan 24 16 : 41 : 41 2018
3Copyright ( c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .
4UDI - 28002 : operation generated ORACLE error 28002
5ORA - 28002 : the password will expire within 7 days
6Connected to : Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - Production
7With the Partitioning , OLAP , Data Mining and Real Application Testing options
8ORA - 39001 : invalid argument value
9ORA - 39000 : bad dump file specification
10ORA - 39143 : dump file "/home/oracle/dump/ora10gCLOB.dmp" may be an original export dump fi8. 使用imp工具导入
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
2LRM - 00101 : unknown parameter name 'REMAP_SCHEMA'
3IMP - 00022 : failed to process parameters , type 'IMP HELP=Y' for help
4IMP - 00000 : Import terminated unsuccessfully
5
6[ oracle @ rhel - ORCL dump ] $ imp hr / hr file = / home / oracle / dump / ora10gCLOB . dmp log = ora10gCLOB . log fromuser = hr touser = oe tablespaces = testlob ignore = y
7Import : Release 11 . 2 . . 3 . 0 - Production on Wed Jan 24 16 : 50 : 42 2018
8Copyright ( c ) 1982 , 2011 , Oracle and / or its affiliates . All rights reserved .
9
10Connected to : Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - Production
11With the Partitioning , OLAP , Data Mining and Real Application Testing options
12
13Export file created by EXPORT : V10 . 02 . 01 via conventional path
14import done in US7ASCII character set and AL16UTF16 NCHAR character set
15import server uses AL32UTF8 character set ( possible charset conversion )
16IMP - 00007 : must be a DBA to import objects to another user 's account
17
18IMP-00000: Import terminated unsuccessfully
19[oracle@rhel-ORCL dump]$imp system/oracle file=/home/oracle/dump/ora10gCLOB.dmp log=ora10gCLOB.log fromuser=hr touser=oe tablespaces=testlob ignore=y
20
21Import: Release 11.2.0.3.0 - Production on Wed Jan 24 16:51:16 2018
22
23Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
24
25
26IMP-00058: ORACLE error 28002 encountered
27ORA-28002: the password will expire within 7 days
28Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
29With the Partitioning, OLAP, Data Mining and Real Application Testing options
30
31Export file created by EXPORT:V10.02.01 via conventional path
32
33Warning: the objects were exported by HR, not by you
34
35import done in US7ASCII character set and AL16UTF16 NCHAR character set
36import server uses AL32UTF8 character set (possible charset conversion)
37. importing HR's objects into OE
38. . importing table "MA_LOB_DUMP" 4 rows imported
39Import terminated successfully with warnings .
40
9. Oracle11g OE用户检查数据
1SQL > conn oe / oe
2Connected .
3SQL > select * from MA_LOB_DUMP ;
4 N C
5--------- ----------
6 1 12
7 1 123
8 2 2345
9 3 asdsada
10. Oracle11g检查LOB段的表空间
1-- 用户的默认表空间
2SQL > select USERNAME , DEFAULT_TABLESPACE from user_users ;
3USERNAME DEFAULT_TABLESPACE
4------------------------------ ------------------------------
5OE TESTLOB
6
7-- TABLE段的默认表空间是USERS
8SQL > select TABLE_NAME , TABLESPACE_NAME from user_tables where table_name = 'MA_LOB_DUMP' ;
9TABLE_NAME TABLESPACE_NAME
10------------------------------ ------------------------------
11MA_LOB_DUMP USERS
12
13-- 移动TABLE到表空间TESTLOB
14SQL > alter table MA_LOB_DUMP move tablespace TESTLOB ;
15Table altered .
16
17SQL > select TABLE_NAME , TABLESPACE_NAME from user_tables where table_name = 'MA_LOB_DUMP' ;
18TABLE_NAME TABLESPACE_NAME
19------------------------------ ------------------------------
20MA_LOB_DUMP TESTLOB
21
22-- LOB段的默认表空间是USERS
23SQL > select TABLE_NAME , TABLESPACE_NAME , SECUREFILE from user_lobs where table_name = 'MA_LOB_DUMP' ;
24
25TABLE_NAME TABLESPACE_NAME SEC
26------------- ----------------- ------------------------------ ---
27MA_LOB_DUMP USERS NO
验证成功，怎么样，是不是很简单呢？
