久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

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

147次閱讀
沒有評論

共計 8857 個字符，預(yù)計需要花費 23 分鐘才能閱讀完成。

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

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

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

創(chuàng)建一個含有 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.

查看一下相關(guān)信息, TESTLOB 表對應(yīng)的表空間為 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í)行導(dǎo)入導(dǎo)出操作

點擊 (此處) 折疊或打開

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)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

正文完
發(fā)表至： 數(shù)據(jù)庫
2023-07-24
 
丸趣
版權(quán)聲明：本站原創(chuàng)文章，由 丸趣 2023-07-24發(fā)表，共計8857字。
轉(zhuǎn)載說明：除特殊說明外本站除技術(shù)相關(guān)以外文章皆由網(wǎng)絡(luò)搜集發(fā)布，轉(zhuǎn)載請注明出處。
評論（沒有評論）
主站蜘蛛池模板： 衢州市| 溧阳市| 丹巴县| 闸北区| 宜章县| 兴文县| 顺义区| 望奎县| 福清市| 且末县| 柘城县| 昌平区| 红原县| 香河县| 平阴县| 昌宁县| 保定市| 利津县| 宣恩县| 西青区| 密山市| 岳西县| 原阳县| 新宁县| 修武县| 盱眙县| 虹口区| 鄂托克前旗| 庆阳市| 通渭县| 尉氏县| 将乐县| 墨玉县| 涪陵区| 冷水江市| 开鲁县| 景泰县| 屯昌县| 博野县| 望江县| 潜江市|