day19——数据迁移
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;
------------------------------------------
1.查看dmp的字符集
[oracle@localhost ~]$ cat 'scott.dmp' |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
0344
SQL> select nls_charset_name(to_number('0344','xxxx')) from dual;
2.查看dmp内容
[oracle@localhost ~]$ strings scott.dmp |grep "CREATE TABLE"|sed -e 's/,/,\n/g'
------------------------------------------
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;(数据又回来了)
==============================================
如果报ORA-39006 ORA-39213
查看安装组建
SQL> select comp_name, version, status from dba_registry;
执行
SQL> execute sys.dbms_metadata_util.load_stylesheets;
---------------------------------
主机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
-----------------------------------------
*****如果报 ora-39006/ora-39213
SQL> select comp_name,version,status from dba_registry;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
SQL> execute sys.dbms_metadata_util.load_stylesheets;
-------------------------------------------------
查看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
