這篇文章將為大家詳細講解有關(guān)使用數(shù)據(jù)泵時LOB字段存放位置在哪,小編覺得挺實用的,因此分享給大家做個參考,希望大家閱讀完這篇文章后可以有所收獲。
我們提供的服務有:成都網(wǎng)站設(shè)計、成都網(wǎng)站制作、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認證、憑祥ssl等。為上千余家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務,是有科學管理、有技術(shù)的憑祥網(wǎng)站制作公司
在遷移過程中,我們很多時候通過數(shù)據(jù)泵方式遷移(expdp/impdp),方便快捷,那么如果涉及到含有LOB字段的表呢,他們是怎么存放呢?
下面 我們就做一個簡單的測試,測試環(huán)境,Oracle11.2.0.4, redhat6.7 x64
創(chuàng)建一個含有l(wèi)ob字段的表,如下:
點擊(此處)折疊或打開
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.
查看一下相關(guān)信息, 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
在當前庫中創(chuàng)建一個新的表空間FIRSOULER,用戶FIRSOULER
并執(zhí)行導入導出操作
點擊(此處)折疊或打開
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
查看建表語句,如下,在創(chuàng)建lob字段表時,如果默認,那么后續(xù)在遷移也會找當時的默認表空間:
點擊(此處)折疊或打開
[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))
關(guān)于“使用數(shù)據(jù)泵時LOB字段存放位置在哪”這篇文章就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,使各位可以學到更多知識,如果覺得文章不錯,請把它分享出去讓更多的人看到。
新聞標題:使用數(shù)據(jù)泵時LOB字段存放位置在哪
鏈接URL:http://muchs.cn/article6/piciog.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供電子商務、靜態(tài)網(wǎng)站、品牌網(wǎng)站設(shè)計、企業(yè)建站、全網(wǎng)營銷推廣、企業(yè)網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)