逻辑迁移
方案二、
采用expdp、impdp方法
1, 使用并行 expdp
sqlplus as sysdba
SQL>create or replace directory export_DIR as '/home/new';
cat bakwithexpdp.shdateexpdp system/oracle@orcl DIRECTORY=export_DIR DUMPFILE=fulldatabase.dmp FULL=Y
job_name=expfulldb parallel=4date
2,使用正常 expdp
cat bak.shdateexpdp system/oracle@orcl DIRECTORY= export_DIR DUMPFILE=fulldatabase.dmp FULL=Y
job_name=expfulldbdate
3,
SQL>create or replace directory export_DIR as '/home/new';
vi impwithimpdp.shdate
impdp system/oracle@rep DIRECTORY= export_DIR DUMPFILE=fulldatabase.dmp FULL=Ydate
方案三、
1,先把符合一定时间条件的数据做成文本,spool成文本
vi unload.sh
2,然后把文本导入数据库
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件(含分隔符). loader.ctl如下:
load data
infile '/home/oracle/backup/script/oradata.txt'
replace into table employees
fields terminated by "," optionally enclosed by '"'
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE "dd-mon-yy",JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
oradata.txt 如下:
7369,"Scott","CLERK",7902, 17-DEC-80, 800.00,,20
7902,"FORD","ANALYST",7566, 17-DEC-80,3000.00,,20
7566,"JONES","MANAGER",7839, 17-DEC-80,2975.00,,20
7839,"KING","PRESIDENT", , 17-DEC-80,5000.00,,10
3,最后把增量部分导进去
并发操作 sqlldr userid=/ control=result1.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true 当加载大量数据时(大约超过10GB),最好抑制日志的产生: SQL>ALTER TABLE employees nologging; 这样不产生REDO LOG,可以提高效率。然后在CONTROL文件中load data上面加一行:unrecoverable, 此选项必须要与DIRECT共同应用。 在并发操作时,ORACLE声称可以达到每小时处理100GB数据的能力!其实,估计能到1-10G就算不错了,开始可用结构相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。
SQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择,它支持变化的加载模式,可选的加载及多表加载.
如何使用 SQL*Loader 工具
我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件(含分隔符). loader.ctl如下:
load data
infile '/home/oracle/backup/script/mydata.csv'
replace into table emp
fields terminated by "," optionally enclosed by '"'
(empno,ename,job,mgr,hiredate date "dd-mon-yy",sal,comm,deptno)
mydata.csv 如下:
7369,"Scott","CLERK",7902, 17-DEC-80, 800.00,,20
7902,"FORD","ANALYST",7566, 17-DEC-80,3000.00,,20
7566,"JONES","MANAGER",7839, 17-DEC-80,2975.00,,20
7839,"KING","PRESIDENT", , 17-DEC-80,5000.00,,10
下面是一个指定记录长度的示例控制文件。"*" 代表数据文件与此文件同名,即在后面使用BEGINDATA段来标识数据。
load data
infile *
append
into table dept
FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'
( deptno,
Dname,
LOC
)
begindata
90 "COMPUTER" "SCIENCE"
91 "ENGLISH" "LITERATURE"
92 "MATHEMATICS"
93 "POLITICAL" "SCIENCE"
Unloader这样的工具
Oracle 没有提供将数据导出到一个文件的工具。但是,我们可以用SQL*Plus的select 及 format 数据来输出到一个文件:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select * from dept;
spool off
另外,也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
当然你也可以使用第三方工具,如SQLWays ,TOAD for Quest等。
加载可变长度或指定长度的记录
如:
create table load_delimited_data(data1 integer,varchar(11));
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是导入固定位置(固定长度)数据示例:
create table load_positional_data(data1 varchar(6),data2 varchar(11));
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳过数据行:
可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如:
LOAD DATA
INFILE *
APPEND
INTO TABLE load_positional_data
SKIP 2
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
33333CCCCCCCCCC
44444DDDDDDDDDD
55555EEEEEEEEEE
66666FFFFFFFFFF
导入数据时修改数据:
在导入数据到数据库时,可以修改数据。注意,这仅适合于常规导入,并不适合 direct导入方式.如:
create table modified_data(region char(2),time_loaded varchar(6),data1 varchar(6),data2 varchar(11),data3 date);
LOAD DATA
INFILE *
INTO TABLE modified_data
(region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
将数据导入多个表:
如:
create table proj(projno integer,empno integer);
LOAD DATA
INFILE *
APPEND
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
BEGINDATA
7910 Land 20 7902 333
导入选定的记录:
如下例: (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
create table my_selective_table(region char(2),service_key integer,call_b_no char(18));
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
导入时跳过某些字段:
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如:
create table T1(field1 char(2),field2 char(3),field char(3));
LOAD DATA
INFILE *
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(field1,
field2 FILLER,
field
)
BEGINDATA
10, "Lan","lio"
导入多行记录:
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
SQL*Loader 数据的提交:
一般情况下是在导入数据文件数据后提交的。
也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQL*Loader 的性能:
1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。
2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然,在很多情况下,不能使用此参数。
3) 通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和 direct 一起使用。
4) 可以同时运行多个导入任务.
常规导入与direct导入方式的区别:
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中。
方案二、
采用DB_LINK方法
1,其实只要网络环境和机器配置好的话
在rep库中创建连接到orcl库的DB LINK
EXPORT ORACLE_SIL=REP
sqlplus / as sysdba
-- Drop existing database link
SQL>drop database link DB_LINK;
-- Create database link
SQL>create database link orcl
connect to system identified by oracle
using 'orcl';
2,在rep库中创建测试表t,设置为nologging
SQL>create table t as select * from dba_objects;
SQL>select COUNT(*) from t;
SQL>delete from t;
SQL>commit;
SQL>select COUNT(*) from t;
SQL>alter table t nologging;
3, 在rep库中简单的INSERT
SQL>alter system set global_names=false scope=spfile;
SQL>insert /*+ append*/ into t select /*+ parallel(t,4)*/ * from dba_objects@db_link;
4,在orcl库中查看dba_objects对象的行数
EXPORT ORACLE_SIL=orcl
sqlplus / as sysdba
SQL>alter system set global_names=false scope=spfile;
SQL>select COUNT(*) from dba_objects;
50736
使用dblink
在平常把能区分出来的大部分数据弄过去
其他少数数据在当机的时候怎么做都可以,甚至exp/imp也可以
方案一、采用exp/imp方法
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。
exp命令可以把数据从远程数据库服务器导出到本地的dmp文件,
imp命令可以把dmp文件从本地导入到远处的数据库服务器中。
利用这个功能可以构建两个相同的数据库,一个用来测试,一个用来正式使用。
执行环境:可以在SQLPLUS.EXE或者(命令行)中执行,
DOS中可以执行时由于 在oracle 10g 中 安装目录\BIN被设置为全局路径,该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写SQLPLUS.EXE,EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类,完成导入导出功能。
下面介绍的是导入导出的实例。
数据导出:
1 将数据库orcl完全导出,用户名sys密码oracle 导出到/home/new/expfulldatabase.dmp中
[oracle@localhost ~]$cat bak.sh
date
exp system/oracle@orcl file=/home/new/expfulldatabase.dmp full=y
date
2 将数据库中hr用户与sys用户的表导出
[oracle@localhost ~]$vi cat bak.sh
date
exp system/oracle@orcl file=/home/new/expschema.dmp owner=(hr)
date
3 将数据库中的表employees导出
[oracle@localhost ~]$vi cat bak.sh
date
exp hr/hr@orcl file= /home/new/exptable.dmp tables=(employees)
date
4 将数据库中的表employees中的字段employee_id大于1000的数据导出
exp hr/hr@orcl file=/home/new/exptablefiltered.dmp tables=(employees) query=\"where employee_id \> 100\"
或者
exp hr/hr@orcl file=/home/new/exptablefiltered.dmp tables=(employees) parfile=exp.par
vi exp.par
query="where employee_id > 100"
上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将/home/new/expfulldatabase.dmp中的数据导入orcl数据库中。
imp system/oracle@orcl file=/home/new/expfulldatabase.dmp
imp system/oracle@orcl full=y file=/home/new/expfulldatabase.dmp ignore=y
上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将/home/new/exptable.dmp中的表employees导入
imp hr/hr@orcl file=/home/new/exptable.dmp tables=(employees) ignore=y
基本上上面的导入导出够用了。不少情况要先是将表彻底删除,然后导入。
注意:
操作者要有足够的权限,权限不够它会提示。
数据库时可以连上的。可以用tnsping orcl来获得数据库orcl能否连上。
附录一:
给用户增加导入数据权限的操作
第一,启动sql*puls
第二,以sys/oracle登陆
第三,create user 用户名 IDENTIFIED BY 密码 (如果已经创建过用户,这步可以省略)
第四,GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
第五, 运行-cmd-进入dmp文件所在的目录,
imp userid=system/manager full=y file=*.dmp
或者 imp userid=system/manager full=y file=filename.dmp
执行示例:
F:\Work\Oracle_Data\backup>imp userid=test/test full=y file=inner_notify.dmp
屏幕显示
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表 "INNER_NOTIFY" 4行被导入
准备启用约束条件...
成功终止导入,但出现警告。
附录二:
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.
先建立import.par,
然后,使用时命令如下:imp parfile=/home/backup/import.par
例 import.par 内容如下:
FROMUSER=hr
TOUSER=sys (注:把表的拥有者由FROMUSER改为TOUSER,FROMUSER和TOUSER的用户可以不同)
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file=/home/new/exptable.dmp
log=/home/new/import_20090910.log
附录三、
提升大数据量导出导入方法
1,imp前,设置noarchive,使trigger失效,去掉索引和约束
alter table table1 disable constraint refer_product_p_id;
alter table table1 disable index idx_name;
alter table table1 disable trigger trig;
2,imp user1/password@db2 file=TABLE1.dmp log=TABLE1.log ignore=y full=y constraints=n grants=n buffer=10485760 commit=y indexs=n direct=y
3,导入后,并行,nologging创建索引。
alter table table1 nologging;
create index ind_name on table1(column) parallel degree;
4,最后添加约束,都用enable novalidate.
alter table table1 enable novalidate constraint refer_product_p_id;
alter table table enable trigger trig