有個客戶跟我說：怎麼将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 fi
8. 使用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[[email protected] 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
驗證成功，怎麼樣，是不是很簡單呢？