2021-08-30

Oracle 数据迁移

批量执行

执行sql文件并记录日志

1
2
3

spool C:\Users\xiong\Desktop\divsion.log
@C:\Users\xiong\Desktop\divsion.sql
spool off

exp/imp方式

优点：兼容性强

按用户导出

1
2

exp 用户名/密码@连接字符信息 file=输出文件路径 log=日志文件路径 owner=用户名（缺省值为当前用户）
exp scott/tiger@orcl file=scott.dmp log=exp_scott.log owner=scott [tables=t1,t2]

导出整个数据库

1	exp system/manager@orcl file=full.dmp log=exp_full.log full=y

按用户导入

1	imp aneat/aneat@orcl file=scott.dmp log=imp_scott.log tablespaces=aneat fromuser=aneat touser=aneat [tables=t1,t2]

导入整个数据库

1	imp system/manager@orcl file=full.dmp log=imp_full.log full=y

expdp/impdp方式（数据泵）

需要Oracle10g版本以上，速度较exp/imp快，导入导出需指定directory(如果新建directory，可能权限要求较高)

创建目录

查看目录

1	select * from dba_directories;

创建新目录

1 2	create directory dumpdir as 'C:\tmp\dumpdir'; grant read,write on directory dumpdir to public;

删除目录

1	drop directory dumpdir;

导出

按用户（Schema）导出
directory=创建的oracle目录名，省略则使用默认DATA_PUMP_DIR
dumpfile=导出文件名称
schemas=导出用户，多个逗号隔开
logfile=日志文件名称，可省略
version=10.2.0.1.0，可选参数指定兼容版本号参数

1	expdp scott/tiger@orcl directory=DATA_PUMP_DIR dumpfile=scott.dmp schemas=scott

按表空间导出

1	expdp scott/tiger@orcl directory=DATA_PUMP_DIR dumpfile=scott_ts.dmp tablespaces=scott,scott_tmp

导出整个数据库

1	expdp \'/ as sysdba\' dumpfile=full.dmp logfile=full.log full=y compression=all

导入

按用户导入

impdp scott/tiger@orcl dumpfile=scott.dmp schemas=scott

-- 如果需要改变用户和表空间
impdp aneat/aneat@orcl dumpfile=scott.dmp remap_schema=scott:aneat remap_tablespace=scott:aneat,scott_tmp:aneat_tmp

按表空间导入

1	impdp scott/tiger@orcl dumpfile=scott_ts.dmp tablespaces=scott

按表导入

1	impdp scott/tiger@orcl dumpfile=full.dmp tables=t1,t2

导入整个数据库

1	impdp \'/ as sysdba\' dumpfile=full.dmp full=y

异常排查

imp导入时，空表未被导入

ORACLE 11G以上版本的新特性，当表无数据时，不分配segment，以节省空间。

查看是否启用该特性

1	show parameter deferred_segment_creation

解决方法

alter system set deferred_segment_creation=false;

alter table tablename allocate extent;
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

impdp全库导入时，报错 ORA-39213

ORA-39006: internal error
ORA-39213: Metadata processing is not available