含有LOB 字段表的迁移示例

本文参考：http://www.cnblogs.com/springside-example/archive/2011/10/25/2529628.html

这里演示2个不同用户之间的一个迁移，使用expdp/impdp 来实现

1.     先创建directory：
SQL> conn / as sysdba
Connected.

SQL> create directory backup as '/home/oracle/backup';

Directory created.

SQL>  set linesize 300
SQL> col DIRECTORY_PATH format a50
SQL> select * from dba_directories;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ --------------------------------------------------
SYS        DATA_PUMP_DIR                  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS        BACKUP                         /home/oracle/backup
SYS        ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/10.2.0/db_1/ccr/state
SYS        ADMIN_DIR                      /ade/aime_stbbp01/oracle/md/admin
SYS        WORK_DIR                       /ade/aime_stbbp01/oracle/work


2.     创建2个测试用的用户，每个用户有独立的表空间。

SQL> CREATE TABLESPACE lob01 DATAFILE  '/data/orcl/lob01.dbf' SIZE 200M AUTOEXTEND OFF; 

Tablespace created.

SQL> CREATE TABLESPACE lob02 DATAFILE  '/data/orcl/lob02.dbf' SIZE 200M AUTOEXTEND OFF;

Tablespace created.

SQL> create user lobtest1 identified by lobtest1 default tablespace lob01;

User created.

SQL> create user lobtest2 identified by lobtest2 default tablespace lob02;

User created.

SQL> grant connect,resource,dba to lobtest1,lobtest2;

Grant succeeded.

3.     登陆anqing1 用户，创建含有LOB的表：

SQL> conn lobtest1/lobtest1;
Connected.
SQL> 
SQL> create table lob1(line number,text clob);

Table created.

SQL> insert into lob1  select line,text from all_source;

300220 rows created.

SQL> commit;

Commit complete.

SQL> col SEGMENT_NAME format a30
SQL> col TABLESPACE_NAME format a15
SQL> col SIZE format a10
SQL> select segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024||'M'as "SIZE" from user_segments group by segment_name,segment_type,tablespace_name;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME SIZE
------------------------------ ------------------ --------------- ----------
LOB1                           TABLE              LOB01           43M
SYS_LOB0000051803C00002     LOBSEGMENT         LOB01           27M
SYS_IL0000051803C00002       LOBINDEX           LOB01           .0625M


这里我们可以看到，我的表LOB1占用空间43M，LOBSEGMENT 占用27M。

4.     采用 REMAP_SCHEMA
4.1 expdp 导出：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

[oracle@test backup]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@test backup]$ expdp lobtest1/lobtest1 directory=backup dumpfile=lobtest1_1.dmp logfile=lobtest1_1.log schemas=lobtest1;

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 23 October, 2015 12:17:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "LOBTEST1"."SYS_EXPORT_SCHEMA_01":  lobtest1/******** directory=backup dumpfile=lobtest1_1.dmp logfile=lobtest1_1.log schemas=lobtest1 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 70 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "LOBTEST1"."LOB1"                           50.93 MB  300220 rows
Master table "LOBTEST1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LOBTEST1.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/lobtest1_1.dmp
Job "LOBTEST1"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:18:09

4.2 impdp 导入：

[oracle@test backup]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@test backup]$ impdp lobtest2/lobtest2 directory=backup dumpfile=lobtest1_1.dmp logfile=lobtest2_1.log remap_schema=lobtest1:lobtest2 table_exists_action=replace; 

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 23 October, 2015 12:20:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "LOBTEST2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "LOBTEST2"."SYS_IMPORT_FULL_01":  lobtest2/******** directory=backup dumpfile=lobtest1_1.dmp logfile=lobtest2_1.log remap_schema=lobtest1:lobtest2 table_exists_action=replace 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LOBTEST2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LOBTEST2"."LOB1"                           50.93 MB  300220 rows
Job "LOBTEST2"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 12:20:38

impdp参数解释：

REMAP_SCHEMA参数：实现将一个用户的的数据迁移到另外一个用户

table_exists_action参数：

使用imp进行数据导入时，若表已经存在，要先drop掉表，再进行导入。

而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：

1)  skip：默认操作

2)  replace：先drop表，然后创建表，最后插入数据

3)  append：在原来数据的基础上增加数据

4)  truncate：先truncate，然后再插入数据

4.3 验证：

SQL> conn lobtest2/lobtest2
Connected.
SQL> 
SQL> 
SQL> col SEGMENT_NAME format a30
SQL> col TABLESPACE_NAME format a15
SQL> col SIZE format a10
SQL> select segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024||'M'as "SIZE" from user_segments group by segment_name,segment_type,tablespace_name;

SEGMENT_NAME                                   SEGMENT_TYPE          TABLESPACE_NAME SIZE
------------------------------                            ------------------                   ---------------                  ----------
LOB1                                                         TABLE                              LOB01                         43M
SYS_LOB0000051927C00002      LOBSEGMENT               LOB01                          27M
SYS_IL0000051927C00002         LOBINDEX                       LOB01                         .0625M

SQL> select owner,table_name,tablespace_name from all_tables where table_name ='LOB1';

OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------------
LOBTEST2   LOB1       LOB01
LOBTEST1   LOB1       LOB01

我们的数据已经导入到了lobtest2用户下，但是该表的物理存储还是存在lobtest1的tablespace下面。

4.4 Move Table

将LOB1从lobtest1 表空间下面Move 到lobtest2下面。

SQL> alter table LOB1 move tablespace lob02;

Table altered.

SQL> select owner,table_name,tablespace_name from all_tables where table_name ='LOB1';

OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------------
LOBTEST2   LOB1       LOB02
LOBTEST1   LOB1       LOB01

SQL> select segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024||'M'as "SIZE" from user_segments group by segment_name,segment_type,tablespace_name;


SEGMENT_NAME                                  SEGMENT_TYPE       TABLESPACE_NAME SIZE
------------------------------                           ------------------               --------------- ----------
LOB1                                                        TABLE                           LOB02           43M
SYS_LOB0000051927C00002      LOBSEGMENT           LOB01           27M
SYS_IL0000051927C00002         LOBINDEX                     LOB01           .0625M

这里的LOB1 表已经移到lobtest2的表空间下了，但是LOBSEGMENT和LOBINDEX 还没有移动。 继续操作：

SQL> alter table lob1 move lob(text) store as (tablespace lob02);

Table altered.

SQL> select segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024||'M' as"SIZE" from user_segments group by segment_name,segment_type,tablespace_name;

SEGMENT_NAME                                    SEGMENT_TYPE       TABLESPACE_NAME SIZE
------------------------------                           ------------------                  --------------- ----------
LOB1                                                         TABLE                            LOB02           43M
SYS_IL0000051927C00002            LOBINDEX                   LOB02          .0625M
SYS_LOB0000051927C00002       LOBSEGMENT             LOB02           27M


我这里演示的是不同用户之间的一个迁移，如果是相同用户下的迁移，只需要在操作之前把相关的表空间和用户建好就可以了。