資料遷移

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 [email protected]:/home/oracle/

[oracle@oracle1 ~]$ scp bcc.dmp [email protected]:/home/oracle/

傳遞檔案結束後 再對端更改權限

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

導入表空間

另一台上

SQL> create user bbb identified by bbb;

SQL> alter user bbb account unlock;

[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 [email protected]:/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;

更改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 資料類型用來存儲兩個時間戳之間的時間間隔)

3.SQL> select * from t1;

SQL> insert into t1 values(999);

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 

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

删除資料庫鍊