真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

使用數(shù)據(jù)泵時(shí)LOB字段存放位置在哪

這篇文章將為大家詳細(xì)講解有關(guān)使用數(shù)據(jù)泵時(shí)LOB字段存放位置在哪，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

我們提供的服務(wù)有：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、憑祥ssl等。為上千余家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的憑祥網(wǎng)站制作公司

在遷移過程中，我們很多時(shí)候通過數(shù)據(jù)泵方式遷移(expdp/impdp)，方便快捷，那么如果涉及到含有LOB字段的表呢，他們是怎么存放呢？

下面 我們就做一個(gè)簡單的測試，測試環(huán)境，Oracle11.2.0.4， redhat6.7 x64

創(chuàng)建一個(gè)含有l(wèi)ob字段的表，如下：

點(diǎn)擊(此處)折疊或打開

  1. SQL> conn test/test

  2. Connected.

  3. SQL> Create table testlob (A number, B clob) LOB(b) STORE AS(TABLESPACE users);   --注意，此處已指定表空間為USERS（當(dāng)然，不建議使用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.

查看一下相關(guān)信息, TESTLOB表對(duì)應(yīng)的表空間為TEST   其lob字段所在表空間為USERS

點(diǎn)擊(此處)折疊或打開

  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


在當(dāng)前庫中創(chuàng)建一個(gè)新的表空間FIRSOULER，用戶FIRSOULER

并執(zhí)行導(dǎo)入導(dǎo)出操作

點(diǎn)擊(此處)折疊或打開

  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


下面生成建表語句：

點(diǎn)擊(此處)折疊或打開

  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 還是原來的表空間

點(diǎn)擊(此處)折疊或打開

  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使用默認(rèn)表空間情況；

點(diǎn)擊(此處)折疊或打開

  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


查看建表語句，如下，在創(chuàng)建lob字段表時(shí)，如果默認(rèn)，那么后續(xù)在遷移也會(huì)找當(dāng)時(shí)的默認(rèn)表空間：

點(diǎn)擊(此處)折疊或打開

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



簡單測試，提醒，在遷移過程中，一定確認(rèn)lob字段所在表空間

下面是沒有LOB字段所在表空間的情況

點(diǎn)擊(此處)折疊或打開

  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也會(huì)改變。原來LOB    字段在USERS表空間中，表的表空間為TEST表空間

點(diǎn)擊(此處)折疊或打開

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

關(guān)于“使用數(shù)據(jù)泵時(shí)LOB字段存放位置在哪”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請把它分享出去讓更多的人看到。


當(dāng)前名稱：使用數(shù)據(jù)泵時(shí)LOB字段存放位置在哪
文章網(wǎng)址：http://weahome.cn/article/piciog.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部