数据迁移

exp/imp 导入导出

1.scott用户登录

2.SQL> create table t1(id int);

SQL> insert into t1 values(1);

SQL> insert into t1 values(2);

SQL> select * from t1

SQL> commit;

3.导出scott 用户

[oracle@sq ~]$ exp \'sys/a123456 as sysdba\' file=/home/oracle/scott.dmp owner=scott direct=y

4.SQL> dorp table t1;

SQL> select * from t1(没内容)

5.导入scott 用户数据

[oracle@sq ~]$ imp \'sys/a123456 as sysdba\' file=/home/oracle/scott.dmp FROMUSER=scott TOUSER=scott

6.SQL> select * from t1(内容又回来了)

------------------------------------------s

导出表

[oracle@oracle1 ~]$ exp scott/abc tables=t1 rows=y file=/home/oracle/t1.dmp

导入表

[oracle@oracle1 ~]$ imp scott/abc tables=t1 rows=y file=/home/oracle/t1.dmp

------------------------------------------

迁移表空间

SQL> create tablespace bbb datafile '/home/oracle/bbb.dbf'  size 10M; 

SQL> create user bbb identified by bbb default tablespace bbb;

SQL> grant connect,resource to bbb;

SQL> conn bbb/bbb

SQL> create table t as select * from all_objects;

检查一下表空间是否齐备

sys下

exec sys.dbms_tts.transport_set_check('bbb', TRUE); 

SQL> select * from sys.transport_set_violations;

no rows selected  表示该表空里的对象集是自包含的(对象及其索引都在此表空间中) 

SQL> alter tablespace bbb read only;

导出表空间

[oracle@oracle1 ~]$ exp \'sys/a123456 as sysdba\' transport_tablespace=y tablespaces=bbb file=/home/oracle/bcc.dmp

[oracle@oracle1 ~]$ scp bbb.dbf root@192.168.8.222:/home/oracle/

[oracle@oracle1 ~]$ scp bcc.dmp root@192.168.8.222:/home/oracle/

传递文件结束后 再对端更改权限

[root@oracle2 oracle]# chown oracle:oinstall ccc.dbf 

导入表空间

另一台上

SQL> create user bbb identified by bbb;

SQL> alter user bbb account unlock;

SQL> grant connect,resource to bbb;

[oracle@oracle2 ~]$ imp \'sys/a123456 as sysdba\' file=/home/oracle/bcc.dmp transport_tablespace=y tablespaces=bbb datafiles=/home/oracle/bbb.dbf fromuser=bbb touser=bbb

******注意*****

表空间导入,导出时 两台数据库块大小一致,字符集一致

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

------------------------------------------

8.5上

(导出t1表)

exp scott/abc tables=t1 rows=y file=/home/oracle/t1.dmp

scp t1.dmp root@192.168.8.2:/home/oracle

8.2上

(导入t1表)

imp scott/abc tables=t1 rows=y file=/home/oracle/t1.dmp 

----------------------------------------

数据泵expdp/impdp

在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项;

     EXP和IMP是客户段工具程序,它们既可以在可以客户端使用,也可以在服务端使用.

     EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用

     IMP只适用于EXP导出文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出文件,而不适用于EXP导出文件.

主机A(ip 8.5)

1.创建数据泵目录

SQL> create directory dump_dir as '/home/oracle/dump';

SQL> grant read,write on directory dump_dir to scott;(赋权)

2.导出

[oracle@sq123 ~]$ expdp scott/abc directory=dump_dir dumpfile=t1.dmp tables=t1;

SQL> drop table t1;

3.导入

[oracle@sq123 ~]$ impdp scott/abc directory=dump_dir dumpfile=t1.dmp tables=t1;

SQL> select * from t1;(数据又回来了)

---------------------------------

主机B(ip 8.2)

网络传输(网络数据链 数据迁移)

1. /u01/app/oracle/product/10.2.0/db_1/network/admin

[oracle@sq admin]$ vi tnsnames.ora (添加对端的连接服务)

TEST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.5)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = TEST)

    )

 )

2.SQL> conn scott/abc123@TEST(测试)

     Connected.

  

  SQL> select count(*) from t1;

3.SQL> conn scott/abc (本地登陆)

SQL> insert into t1@TEST select * from t1@TEST; (错误没有用户验证,下面建立的链接能够解决)  

  SQL> create database link db85 connect to scott identified by abc123 using 'TEST';(没有成功,没有权限)

 

sys登陆

SQL> grant create database link to scott;

scott下 再次运行

 SQL> create database link db85 connect to scott identified by abc123 using 'TEST';

4.SQL> insert into t1@db85 select * from t1@db85;

5.迁移数据

SQL> create table t1 as select * from t1@db85;

SQL> commit;

------------------------------------------

更改B时 A上的t1表也更新

B主机(ip 8.2)(scott登陆)

1.创建一个存储过程

SQL> create procedure pro_insert_t1

  2  as

  3  begin

  4  insert into t1@db85 select * from t1 where id not in (select id from t1@db85);

  5  commit;

  6  end;

  7  /

(第4行 where后是 id列 不等于括号中的值)

(弟4行 插入到 85中t1表,数据参考本地t1)

(create table srct(id int);

 insert into srct values(999);

 create table destt(id int);

insert into destt select * from srct;(destt表中有了数据从srct中)

insert into destt select * from srct where id not in(select id from destt); (destt中已经有的数据不再次插入一边))

2.将这个存储过程放到job里面

SQL> variable job1 number; (时间计算方法)

SQL> begin

  2  sys.dbms_job.submit(job => :job1,

  3                      what => 'pro_insert_t1;',

  4                      next_date => sysdate,

  5                      interval => 'SYSDATE+1/1440');

  6  commit;

  7  end;

  8  /

( 定义一系列一起执行的 Transact-SQL 语句)

(NTERVAL 数据类型用来存储两个时间戳之间的时间间隔)

SQL> commit;

3.SQL> select * from t1;

SQL> insert into t1 values(999);

SQL> commit;

SQL> select * from t1@db85;(表中也会多一条 999的数据)

==========================================================

数据库链 结合expdp/impdp 迁移数据 （导出用户）

B主机(ip 8.2)

SQL> conn system/abc123

Connected.

建立数据库链

SQL> create public database link db851 connect to system identified by abc123 using 'TEST';

SQL> drop user scott cascade;

查看用户

SQL> select username from dba_users;

[oracle@sq ~]$ impdp system/abc123 network_link=db851 schemas=scott

(导出前 确保服务器端用户的默认表空间跟目标的一样)

（导出单表）

[oracle@sq admin]$ impdp system/abc123 network_link=db851 tables=scott.t1

-----------------------------------------

查看scott用户的会话连接

SQL> select sid,serial#,username, machine,status from v$session where username like '%SCOTT%';

结束会话

SQL> alter system kill session '146,1188' immediate;

('146,1188' 为sid,serial#)

查看默认表空间

select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users

改变默认临时表空间

SQL> alter database default temporary tablespace temp;

---------------------------------------------- 

全库导出

[oracle@oracle1 admin]$ exp \'sys/a123456 as sysdba\' file=/home/oracle/ff.dmp full=y

全库导入

imp \'sys/a123456 as sysdba\' full=y file=/home/oracle/ff.dmp ignore=y 

--------------------------

删除数据库链

SQL> SELECT * FROM USER_DB_LINKS;

SQL> DROP DATABASE LINK YANGTK.YANGTK

     本文转自陈继松 51CTO博客，原文链接：http://blog.51cto.com/chenjisong/1737396，如需转载请自行联系原作者