ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【LOB】使用数据泵时 LOB 字段存放位置

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

原创 Oracle 作者：xysoul_云龙 时间：2017-09-29 16:11:54 0 删除 编辑

在迁移过程中，我们很多时候通过数据泵方式迁移(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表空间，这里仅仅测试 ）

  5. Table created .

  7. SQL > insert into testlob select object_id ,object_name from dba_objects where object_id is not null ;

  9. 86387 rows created .

  11. SQL > commit ;

  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' ;

  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$$


  15. SQL >
  16. SQL > select table_name ,owner ,tablespace_name from dba_tables where table_name = 'TESTLOB' ;

  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

  3. Export : Release 11 .2 .0 .4 .0 - Production on Fri Sep 29 15 :32 :00 2017

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

  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

  3. Import : Release 11 .2 .0 .4 .0 - Production on Fri Sep 29 15 :33 :52 2017

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

  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 ) ;

  7. Table created .

  9. SQL > insert into testlob select object_id ,object_name from dba_objects where object_id is not null ;

  11. 86390 rows created .

  13. SQL > commit ;

  15. Commit complete .

  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' ;

  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$$


  31. SQL >
  32. SQL > select table_name ,owner ,tablespace_name from dba_tables where table_name = 'TESTLOB' ;

  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

  3. Import : Release 11 .2 .0 .4 .0 - Production on Fri Sep 29 15 :42 :11 2017

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

  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

  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

  3. Import : Release 11 .2 .0 .4 .0 - Production on Fri Sep 29 15 :52 :58 2017

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

  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

  3. Import : Release 11 .2 .0 .4 .0 - Production on Fri Sep 29 16 :04 :23 2017

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

  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

  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博客 ” ，链接：https://blog.itpub.net/29487349/viewspace-2145607/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【LISTENER】数据库连接串的几种写法
下一篇： 【BUG】当使用TTS（传输表空间时）从其他平台到HP可造成索引组织表损坏
请登录后发表评论 登录
全部评论
xysoul_云龙
专家姓名：杨云龙
主要从事Oracle数据库 DBA相关工作，其他操作系统、中间件等也有涉及，热衷分享。岁月老将至，如有幸在江湖中留点踪迹，也是欣慰。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博：文盲筱烨 微信公众号：筱烨视点

注册时间：2014-02-15

  • 博文量
    412
  • 访问量
    1104604

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593