【LOB】使用数据泵时 LOB 字段存放位置

在迁移过程中，我们很多时候通过数据泵方式迁移(expdp/impdp)，方便快捷，那么如果涉及到含有LOB字段的表呢，他们是怎么存放呢？

下面 我们就做一个简单的测试，测试环境，Oracle11.2.0.4， redhat6.7 x64

创建一个含有lob字段的表，如下：

点击(此处)折叠或打开

1. SQL> conn test/test
   
2. Connected.
   
3. SQL> Create table testlob (A number, B clob) LOB(b) STORE AS(TABLESPACE users);   --注意，此处已指定表空间为USERS（当然，不建议使用USERS表空间，这里仅仅测试 ）
   
4. 
   
5. Table created.
   
6. 
   
7. SQL> insert into testlob select object_id,object_name from dba_objects where object_id is not null;
   
8. 
   
9. 86387 rows created.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.

查看一下相关信息, TESTLOB表对应的表空间为TEST   其lob字段所在表空间为USERS

点击(此处)折叠或打开

1. SQL> set lines 2000
   
2. SQL> col owner for a15
   
3. SQL> col table_name for a15
   
4. SQL> col column_name for a10
   
5. SQL> col segment_name for a15
   
6. SQL> col index_name for a15
   
7. SQL> select owner,table_name,column_name,segment_name,index_name,tablespace_name from dba_lobs where table_name='TESTLOB';
   
8. 
   
9. OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
   
10. --------------- --------------- ---------- --------------- --------------- ------------------------------
    
11. TEST TESTLOB B SYS_LOB00000880 SYS_IL000008802 USERS
    
12.                                            26C00002$$ 6C00002$$
    
13. 
    
14. 
    
15. SQL>
    
16. SQL> select table_name,owner,tablespace_name from dba_tables where table_name='TESTLOB';
    
17. 
    
18. TABLE_NAME OWNER TABLESPACE_NAME
    
19. --------------- --------------- ------------------------------
    
20. TESTLOB TEST TEST

在当前库中创建一个新的表空间FIRSOULER，用户FIRSOULER

并执行导入导出操作

点击(此处)折叠或打开

1. oracle@mystandby dump]$ expdp test/test directory=sh_dmp DUMPFILE=testlob_ddl.dmp tables=testlob logfile=testlob.log content=METADATA_ONLY
   
2. 
   
3. Export: Release 11.2.0.4.0 - Production on Fri Sep 29 15:32:00 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=sh_dmp DUMPFILE=testlob_ddl.dmp tables=testlob logfile=testlob.log content=METADATA_ONLY
   
10. Processing object type TABLE_EXPORT/TABLE/TABLE
    
11. Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    
12. ******************************************************************************
    
13. Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
    
14.   /backup/dump/testlob_ddl.dmp
    
15. Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 29 15:32:11 2017 elapsed 0 00:00:11

下面生成建表语句：

点击(此处)折叠或打开

1. [oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob.dmp sqlfile=testlob01.sql
   
2. 
   
3. Import: Release 11.2.0.4.0 - Production on Fri Sep 29 15:33:52 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. Master table "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
   
10. Starting "FIRSOULER"."SYS_SQL_FILE_FULL_01": firsouler/******** directory=sh_dmp dumpfile=testlob.dmp sqlfile=testlob01.sql
    
11. Processing object type TABLE_EXPORT/TABLE/TABLE
    
12. Job "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Sep 29 15:33:54 2017 elapsed 0 00:00:01

LOB 还是原来的表空间

点击(此处)折叠或打开

1. CREATE TABLE "TEST"."TESTLOB"
   
2.    ( "A" NUMBER,
   
3.         "B" CLOB
   
4.    ) SEGMENT CREATION IMMEDIATE
   
5.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
   
6.  NOCOMPRESS LOGGING
   
7.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   
8.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   
9.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   
10.   TABLESPACE "TEST"
    
11.  LOB ("B") STORE AS BASICFILE (
    
12.   TABLESPACE "FIRSOULER" ENABLE STORAGE IN ROW CHUNK 8192
    
13.   NOCACHE LOGGING
    
14.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
15.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
16.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

下面再测试一下，lob使用默认表空间情况；

点击(此处)折叠或打开

1. SQL> conn test/test
   
2. Connected.
   
3. SQL>
   
4. SQL>
   
5. SQL> Create table testlob (A number, B clob) LOB(b) STORE AS(enable storage in row);
   
6. 
   
7. Table created.
   
8. 
   
9. SQL> insert into testlob select object_id,object_name from dba_objects where object_id is not null;
   
10. 
    
11. 86390 rows created.
    
12. 
    
13. SQL> commit;
    
14. 
    
15. Commit complete.
    
16. 
    
17. SQL> set lines 2000
    
18. SQL> col owner for a15
    
19. SQL> col table_name for a15
    
20. SQL> col column_name for a10
    
21. SQL> col segment_name for a15
    
22. SQL> col index_name for a15
    
23. SQL> select owner,table_name,column_name,segment_name,index_name,tablespace_name from dba_lobs where table_name='TESTLOB';
    
24. 
    
25. OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
    
26. --------------- --------------- ---------- --------------- --------------- ------------------------------
    
27. TEST TESTLOB B SYS_LOB00000882 SYS_IL000008821 TEST
    
28.                                            13C00002$$ 3C00002$$
    
29. 
    
30. 
    
31. SQL>
    
32. SQL> select table_name,owner,tablespace_name from dba_tables where table_name='TESTLOB';
    
33. 
    
34. TABLE_NAME OWNER TABLESPACE_NAME
    
35. --------------- --------------- ------------------------------
    
36. TESTLOB TEST TEST

查看建表语句，如下，在创建lob字段表时，如果默认，那么后续在迁移也会找当时的默认表空间：

点击(此处)折叠或打开

1. [oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp sqlfile=testlob001.sql
   
2. 
   
3. Import: Release 11.2.0.4.0 - Production on Fri Sep 29 15:42:11 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. Master table "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
   
10. Starting "FIRSOULER"."SYS_SQL_FILE_FULL_01": firsouler/******** directory=sh_dmp dumpfile=testlob01.dmp sqlfile=testlob001.sql
    
11. Processing object type TABLE_EXPORT/TABLE/TABLE
    
12. Job "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Sep 29 15:42:13 2017 elapsed 0 00:00:01
    
13. 
    
14. [oracle@mystandby dump]$ cat testlob001.sql
    
15. -- CONNECT FIRSOULER
    
16. ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
17. ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
18. ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
19. ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
20. ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
21. ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
    
22. -- new object type path: TABLE_EXPORT/TABLE/TABLE
    
23. CREATE TABLE "TEST"."TESTLOB"
    
24.    ( "A" NUMBER,
    
25.         "B" CLOB
    
26.    ) SEGMENT CREATION IMMEDIATE
    
27.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    
28.  NOCOMPRESS LOGGING
    
29.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
30.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
31.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    
32.   TABLESPACE "TEST"
    
33.  LOB ("B") STORE AS BASICFILE (
    
34.   TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
    
35.   NOCACHE LOGGING
    
36.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
37.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
38.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
    
39. [oracle@mystandby dump]$

简单测试，提醒，在迁移过程中，一定确认lob字段所在表空间

下面是没有LOB字段所在表空间的情况

点击(此处)折叠或打开

1. [oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp remap_tablespace=firsouler:test logfile=implob.log
   
2. 
   
3. Import: Release 11.2.0.4.0 - Production on Fri Sep 29 15:52:58 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. ORA-31626: job does not exist
   
10. ORA-31633: unable to create master table "FIRSOULER.SYS_IMPORT_FULL_05"
    
11. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    
12. ORA-06512: at "SYS.KUPV$FT", line 1038
    
13. ORA-00959: tablespace 'FIRSOULER' does not exist

那么改变表空间呢，通过expdp  remap_tablespace 改变，lob也会改变。原来LOB    字段在USERS表空间中，表的表空间为TEST表空间

点击(此处)折叠或打开

1. [oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp remap_schema=test:firsouler remap_tablespace=test:firsouler sqlfile=testlob001.sql
   
2. 
   
3. Import: Release 11.2.0.4.0 - Production on Fri Sep 29 16:04:23 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
9. Master table "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
   
10. Starting "FIRSOULER"."SYS_SQL_FILE_FULL_01": firsouler/******** directory=sh_dmp dumpfile=testlob01.dmp remap_schema=test:firsouler remap_tablespace=test:firsouler sqlfile=testlob001.sql
    
11. Processing object type TABLE_EXPORT/TABLE/TABLE
    
12. Job "FIRSOULER"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Sep 29 16:04:25 2017 elapsed 0 00:00:01
    
13. 
    
14. [oracle@mystandby dump]$ cat testlob0
    
15. testlob001.sql testlob01.dmp
    
16. [oracle@mystandby dump]$ cat testlob001.sql
    
17. -- CONNECT FIRSOULER
    
18. ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
19. ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
20. ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
21. ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
22. ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
    
23. ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
    
24. -- new object type path: TABLE_EXPORT/TABLE/TABLE
    
25. CREATE TABLE "FIRSOULER"."TESTLOB"
    
26.    ( "A" NUMBER,
    
27.         "B" CLOB
    
28.    ) SEGMENT CREATION IMMEDIATE
    
29.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    
30.  NOCOMPRESS LOGGING
    
31.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
32.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
33.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    
34.   TABLESPACE "FIRSOULER"
    
35.  LOB ("B") STORE AS BASICFILE (
    
36.   TABLESPACE "FIRSOULER" ENABLE STORAGE IN ROW CHUNK 8192
    
37.   NOCACHE LOGGING
    
38.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
39.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
40.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

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

转载于:http://blog.itpub.net/29487349/viewspace-2145607/