Oracle数据库迁移方式一：impdp+dblink
实验环境：
源库： 192.168.2.200 SID=testdb
目标库：192.168.2.100 SID=testdb
实验目的：
使用impdp+dblink的方式 将192.168.2.200数据库上的jtrms用户的所有数据导入到目标数据库192.168.2.100
注意：使用impdp+dblink 的方式，这样就节省了数据导出（expdp），然后再导入的过程。
实施步骤：
一、源库操作：
1、首先检查源库要迁移的用户信息：
SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'JTRMS'; ---检查用户的默认表空间
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
JTRMS TEST
2、该用户的表空间大小
SQL> select TABLESPACE_NAME,BYTES from dba_data_files where TABLESPACE_NAME like 'TEST';
TABLESPACE_NAME BYTES
------------------------------ ----------
TEST 314572800
SQL> show user;
USER is "JTRMS"
SQL> select * from tab; ----检查该用户一共有97张表
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ECR_ANNOUNCEMENT TABLE
ECR_APPROVAL TABLE
ECR_APPROVAL_TO_RECEIVE TABLE
ECR_BP_SYN TABLE
ECR_CHANGE TABLE
JFORUM_THEMES TABLE
JFORUM_TOPICS TABLE
JFORUM_TOPICS_WATCH TABLE
JFORUM_USERS TABLE
JFORUM_USER_GROUPS TABLE
JFORUM_VOTE_DESC TABLE
JFORUM_VOTE_RESULTS TABLE
JFORUM_VOTE_VOTERS TABLE
JFORUM_WORDS TABLE
SYS_TEMP_FBT TABLE
。。。。。。。
。。。。。。。
97 rows selected.
3、查看该用户下的索引信息：
SQL> set linesize 300 pagesize 300
SQL> select INDEX_NAME,TABLE_OWNER,STATUS,NUM_ROWS from user_indexes;
4、查看该用户下所有的job信息
alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss';
set linesize 300 pagesize 300
col what for a50
col interval for a50
select job,what,LAST_DATE,NEXT_DATE,SCHEMA_USER,interval from user_jobs;
二、在目标数据库上操作
注意：在目标数据库创建用户信息之前，一定要检查目标看是否有该用户的信息，和表空间是否有重名等
1、在目标数据库上创建用户信息---一切以源库为标准
SQL> create tablespace test datafile '/opt/oracle/test.dbf' size 300M; ---创建test表空间
SQL> create user jtrms identified by jtrms default tablespace test; ---创建jtrms用户
SQL> grant connect,resource to jtrms; ---对该用户授权
SQL> grant dba to jtrms;
2、在目标数据库增加源库的tns文件信息，并测试连接源库
[oracle@test ~]$ vim tnsnames.ora
testdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
---注意，增加的是源库192.168.2.200的信息
[oracle@test ~]$ sqlplus jtrms/jtrms@testdb ; ----使用jtrms用户连接源库192.168.2.200
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 00:54:31 2017
Copyright (c) 1982, 2013, Oracle. 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
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
testdb
3、在目标数据库上创建dblink
create public database link testlink connect to jtrms identified by jtrms
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
注意：该dblink是连接源库用的
4、在目标数据库上，开始使用下面的命令将源库jtrms用户的数据使用dblink导入到目标库
[oracle@test ~]$ impdp jtrms network_link=testlink schemas=jtrms cluster=N PARALLEL=2
Import: Release 11.2.0.4.0 - Production on Mon Jun 5 01:03:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password: ---输入用户的密码
5、登录数据库，验证数据是否传输成功：
[oracle@test ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 01:06:34 2017
Copyright (c) 1982, 2013, Oracle. 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
SQL> conn jtrms/jtrms
SQL> select count(*) from tab;
COUNT(*)
----------
97
SQL> set linesize 300 pagesize 300
SQL> select INDEX_NAME,TABLE_OWNER,STATUS,NUM_ROWS from user_indexes;
INDEX_NAME TABLE_OWNER STATUS NUM_ROWS
------------------------------ ------------------------------ -------- ----------
IDX_BOK_REL JTRMS VALID 0
IDX_BOK_USER JTRMS VALID 0
SYS_C0011373 JTRMS VALID 0
SYS_C0011372 JTRMS VALID 0
IDX_BANLIST_EMAIL JTRMS VALID 0
-----至此，实施完成；
