Shulou(Shulou.com)05/31 Report--

This article will explain in detail where the LOB field is stored when using the data pump. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

In the process of migration, we often migrate (expdp/impdp) through a data pump, which is convenient and fast, so if tables with LOB fields are involved, how do they store them?

Let's do a simple test, test environment, Oracle11.2.0.4, redhat6.7 x64

Create a table with the lob field, as follows:

Click (here) to collapse or open

SQL > conn test/test

Connected.

SQL > Create table testlob (A number, B clob) LOB (b) STORE AS (TABLESPACE users);-- Note that the tablespace has been specified as USERS here (of course, USERS tablespace is not recommended, just test here)

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.

Check the relevant information. The table space of TESTLOB table is TEST, and the table space of lob field is USERS.

Click (here) to collapse or open

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

Create a new tablespace FIRSOULER in the current library, user FIRSOULER

And perform the import and export operation

Click (here) to collapse or open

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

The following table creation statements are generated:

Click (here) to collapse or open

[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 is still the original tablespace.

Click (here) to collapse or open

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

Let's test again how lob uses the default tablespace.

Click (here) to collapse or open

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

Check the table creation statement as follows. If the lob field table is created by default, then the default table space will be found in the subsequent migration:

Click (here) to collapse or open

[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 199'

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

Simple testing, reminder, during the migration process, be sure to confirm that the lob field is in the tablespace

The following is the case where there is no tablespace for the LOB field

Click (here) to collapse or open

[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

Then change the tablespace, through the expdp remap_tablespace change, lob will also change. The original LOB field is in the USERS table space, and the table space of the table is the TEST table space.

Click (here) to collapse or open

[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 199'

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

This is the end of the article on "where the LOB field is stored when using the data pump". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.