使用數據泵時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))

關于“使用數據泵時LOB字段存放位置在哪”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。