本文参考: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
我这里演示的是不同用户之间的一个迁移,如果是相同用户下的迁移,只需要在操作之前把相关的表空间和用户建好就可以了。