【LOB】使用資料泵時 LOB 欄位存放位置
在遷移過程中,我們很多時候透過資料泵方式遷移(expdp/impdp),方便快捷,那麼如果涉及到含有LOB欄位的表呢,他們是怎麼存放呢?
下面 我們就做一個簡單的測試,測試環境,Oracle11.2.0.4, redhat6.7 x64
建立一個含有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 .
檢視一下相關資訊, 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
在當前庫中建立一個新的表空間FIRSOULER,使用者FIRSOULER
並執行匯入匯出操作
點選( 此處)摺疊或開啟
-
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
檢視建表語句,如下,在建立lob欄位表時,如果預設,那麼後續在遷移也會找當時的預設表空間:
點選( 此處)摺疊或開啟
-
[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 ) )
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-2145607/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- LOB欄位資料清理 - 更新為null後move lobNull
- LOB欄位在Oracle中的存放方式。Oracle
- oracle中lob欄位Oracle
- LOB欄位EMPTY_LOB和NULL的區別Null
- Oracle 建表時LOB欄位語法Oracle
- ORACLE LOB大欄位維護Oracle
- 釋放大資料量的lob欄位空間大資料
- lob欄位表空間遷移
- Oracle lob載入bfile資料到blob欄位中Oracle
- 帶有LOB欄位的表遷移
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- Oracle 帶LOB欄位的表的遷移Oracle
- 小議lob欄位結構和儲存
- lob欄位的ora-1555處理方案
- DatabaseLink不支援merge和lob欄位查詢Database
- lob欄位型別轉換ora-22858型別
- 一次PLSQL處理LOB欄位的優化SQL優化
- 可以處理LOB欄位的常用字元函式字元函式
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- 遷移帶LOB等大欄位資料到非預設表空間
- 【LOB】Oracle lob管理常用語句Oracle
- Long -> lob , to_lob 轉換,遷移
- Oracle - LOB(大物件資料型別)Oracle物件資料型別
- 通過SQLLDR匯入LOB資料SQL
- [重慶思莊每日技術分享]-重建LOB欄位上的IndexIndex
- Oracle LOBOracle
- Oracle LOB資料型別的處理Oracle資料型別
- 用sqlloader(sqlldr)裝載LOB資料SQL
- ORACLE 10G下LOB_DATA欄位型別佔滿臨時表空間問題Oracle 10g型別
- AWR取樣資料存放位置
- LOB型別型別
- Oracle LOB issueOracle
- LOB(large object)Object
- LOB學習
- [20140729]關於LOB欄位儲存特性1.txt
- [20140729]關於LOB欄位儲存特性2.txt
- [20140729]關於LOB欄位儲存特性3.txt
- 行連結與行遷移, LOB欄位的儲存及效能影響