共计 8857 个字符，预计需要花费 23 分钟才能阅读完成。
这篇文章将为大家详细讲解有关使用数据泵时 LOB 字段存放位置在哪，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
在迁移过程中，我们很多时候通过数据泵方式迁移(expdp/impdp)，方便快捷，那么如果涉及到含有 LOB 字段的表呢，他们是怎么存放呢？
下面 我们就做一个简单的测试，测试环境，Oracle11.2.0.4，redhat6.7 x64
创建一个含有 lob 字段的表，如下：
点击 (此处) 折叠或打开
SQL conn test/test
Connected.
SQL Create table testlob (A number, B clob) LOB(b) STORE AS(TABLESPACE users); – 注意，此处已指定表空间为 USERS（当然，不建议使用 USERS 表空间，这里仅仅测试 ）
Table created.
SQL insert into testlob select object_id,object_name from dba_objects where object_id is not null;
86387 rows created.
SQL commit;
Commit complete.
查看一下相关信息, TESTLOB 表对应的表空间为 TEST 其 lob 字段所在表空间为 USERS
点击 (此处) 折叠或打开
SQL set lines 2000
SQL col owner for a15
SQL col table_name for a15
SQL col column_name for a10
SQL col segment_name for a15
SQL col index_name for a15
SQL select owner,table_name,column_name,segment_name,index_name,tablespace_name from dba_lobs where table_name= TESTLOB
OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
————— ————— ———- ————— ————— ——————————
TEST TESTLOB B SYS_LOB00000880 SYS_IL000008802 USERS
26C00002$$ 6C00002$$
SQL
SQL select table_name,owner,tablespace_name from dba_tables where table_name= TESTLOB
TABLE_NAME OWNER TABLESPACE_NAME
————— ————— ——————————
TESTLOB TEST TEST
在当前库中创建一个新的表空间 FIRSOULER，用户 FIRSOULER
并执行导入导出操作
点击 (此处) 折叠或打开
oracle@mystandby dump]$ expdp test/test directory=sh_dmp DUMPFILE=testlob_ddl.dmp tables=testlob logfile=testlob.log content=METADATA_ONLY
Export: Release 11.2.0.4.0 – Production on Fri Sep 29 15:32:00 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting TEST . SYS_EXPORT_TABLE_01 : test/******** directory=sh_dmp DUMPFILE=testlob_ddl.dmp tables=testlob logfile=testlob.log content=METADATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table TEST . SYS_EXPORT_TABLE_01 successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/backup/dump/testlob_ddl.dmp
Job TEST . SYS_EXPORT_TABLE_01 successfully completed at Fri Sep 29 15:32:11 2017 elapsed 0 00:00:11
下面生成建表语句：
点击 (此处) 折叠或打开
[oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob.dmp sqlfile=testlob01.sql
Import: Release 11.2.0.4.0 – Production on Fri Sep 29 15:33:52 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table FIRSOULER . SYS_SQL_FILE_FULL_01 successfully loaded/unloaded
Starting FIRSOULER . SYS_SQL_FILE_FULL_01 : firsouler/******** directory=sh_dmp dumpfile=testlob.dmp sqlfile=testlob01.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job FIRSOULER . SYS_SQL_FILE_FULL_01 successfully completed at Fri Sep 29 15:33:54 2017 elapsed 0 00:00:01
LOB 还是原来的表空间
点击 (此处) 折叠或打开
CREATE TABLE TEST . TESTLOB
(A NUMBER,
B CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE TEST
LOB (B) STORE AS BASICFILE (
TABLESPACE FIRSOULER ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
下面再测试一下，lob 使用默认表空间情况；
点击 (此处) 折叠或打开
SQL conn test/test
Connected.
SQL
SQL
SQL Create table testlob (A number, B clob) LOB(b) STORE AS(enable storage in row);
Table created.
SQL insert into testlob select object_id,object_name from dba_objects where object_id is not null;
86390 rows created.
SQL commit;
Commit complete.
SQL set lines 2000
SQL col owner for a15
SQL col table_name for a15
SQL col column_name for a10
SQL col segment_name for a15
SQL col index_name for a15
SQL select owner,table_name,column_name,segment_name,index_name,tablespace_name from dba_lobs where table_name= TESTLOB
OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
————— ————— ———- ————— ————— ——————————
TEST TESTLOB B SYS_LOB00000882 SYS_IL000008821 TEST
13C00002$$ 3C00002$$
SQL
SQL select table_name,owner,tablespace_name from dba_tables where table_name= TESTLOB
TABLE_NAME OWNER TABLESPACE_NAME
————— ————— ——————————
TESTLOB TEST TEST
查看建表语句，如下，在创建 lob 字段表时，如果默认，那么后续在迁移也会找当时的默认表空间：
点击 (此处) 折叠或打开
[oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp sqlfile=testlob001.sql
Import: Release 11.2.0.4.0 – Production on Fri Sep 29 15:42:11 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table FIRSOULER . SYS_SQL_FILE_FULL_01 successfully loaded/unloaded
Starting FIRSOULER . SYS_SQL_FILE_FULL_01 : firsouler/******** directory=sh_dmp dumpfile=testlob01.dmp sqlfile=testlob001.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job FIRSOULER . SYS_SQL_FILE_FULL_01 successfully completed at Fri Sep 29 15:42:13 2017 elapsed 0 00:00:01
[oracle@mystandby dump]$ cat testlob001.sql
— CONNECT FIRSOULER
ALTER SESSION SET EVENTS 10150 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10904 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 25475 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10407 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10851 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 22830 TRACE NAME CONTEXT FOREVER, LEVEL 192
— new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE TEST . TESTLOB
(A NUMBER,
B CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE TEST
LOB (B) STORE AS BASICFILE (
TABLESPACE TEST ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
[oracle@mystandby dump]$
简单测试，提醒，在迁移过程中，一定确认 lob 字段所在表空间
下面是没有 LOB 字段所在表空间的情况
点击 (此处) 折叠或打开
[oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp remap_tablespace=firsouler:test logfile=implob.log
Import: Release 11.2.0.4.0 – Production on Fri Sep 29 15:52:58 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table FIRSOULER.SYS_IMPORT_FULL_05
ORA-06512: at SYS.DBMS_SYS_ERROR , line 95
ORA-06512: at SYS.KUPV$FT , line 1038
ORA-00959: tablespace FIRSOULER does not exist
那么改变表空间呢，通过 expdp remap_tablespace 改变，lob 也会改变。原来 LOB 字段在 USERS 表空间中，表的表空间为 TEST 表空间
点击 (此处) 折叠或打开
[oracle@mystandby dump]$ impdp firsouler/abc directory=sh_dmp dumpfile=testlob01.dmp remap_schema=test:firsouler remap_tablespace=test:firsouler sqlfile=testlob001.sql
Import: Release 11.2.0.4.0 – Production on Fri Sep 29 16:04:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table FIRSOULER . SYS_SQL_FILE_FULL_01 successfully loaded/unloaded
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
Processing object type TABLE_EXPORT/TABLE/TABLE
Job FIRSOULER . SYS_SQL_FILE_FULL_01 successfully completed at Fri Sep 29 16:04:25 2017 elapsed 0 00:00:01
[oracle@mystandby dump]$ cat testlob0
testlob001.sql testlob01.dmp
[oracle@mystandby dump]$ cat testlob001.sql
— CONNECT FIRSOULER
ALTER SESSION SET EVENTS 10150 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10904 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 25475 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10407 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 10851 TRACE NAME CONTEXT FOREVER, LEVEL 1
ALTER SESSION SET EVENTS 22830 TRACE NAME CONTEXT FOREVER, LEVEL 192
— new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE FIRSOULER . TESTLOB
(A NUMBER,
B CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE FIRSOULER
LOB (B) STORE AS BASICFILE (
TABLESPACE FIRSOULER ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
关于“使用数据泵时 LOB 字段存放位置在哪”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
