oracle 导入导出

使用exp/imp导出导入

-- 全用户导出
exp 'sys/sys@ORCL as sysdba' file=c:\104.dmp  log =c:\104.log owner=(jxzzwx2,zzwxoa,zzcrbt)
-- 导入
imp 'sys/sys@ORCL as sysdba' file=c:\104.dmp  log =c:\104.log ignore
-- 查询表空间建立sql 用sysdba查询
select dbms_metadata.get_ddl('TABLESPACE','TBS_JXCRBT_DATA') FROM DUAL;

`
-- 导出时客户端字符集不对导致导入的时候会报错
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing ZZCRBT's objects into ZZCRBT
. . importing table "ZZWX_CRBT_OPER_REPORT" 84 rows imported
. . importing table "ZZWX_CRBT_RING_REPORT" 7765 rows imported
. . importing table "ZZWX_CRBT_SINGER" 33184 rows imported
. . importing table "ZZWX_CRBT_TONE_BOX_INFO" 247 rows imported
. . importing table "ZZWX_CRBT_TONE_INFO" 168654 rows imported
. . importing table "ZZWX_CRBT_USER_TONE_BOX_MEM" 1458 rows imported
. . importing table "ZZWX_MANAGER_FUNC"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "ZZCRBT"."ZZWX_MANAGER_FUNC"."VCBUSINESSNAME" (actual: 18, maximum: 16)
Column 1 3205
Column 2 用户铃音列表
Column 3 用户铃音列表
Column 4 ../single/userRing.jsp

-- windows oracle客户端编码修改
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient11g_home1下NLS_LANG

使用expdp/impdp导出导入

参考地址:
Oracle expdp/impdp导出导入命令及数据库备份
oracle 11g expdp impdp详细使用方法

-- 创建目录
create directory expdir as '/home/oracle/expdp_dir'

-- expdp按用户导出导入
expdp \'jxzzwx2/jxzzwx2@ZZWX4\' schemas=jxzzwx2,zzcrbt dumpfile=20150204.exp.dmp directory=expdir
-- 不存在的用户会自动创建
impdp \'sys/sys as sysdba\' directory=impdir dumpfile=20150204.exp.dmp REMAP_SCHEMA=jxzzwx2:jxzzwx2_new,zzcrbt:zzcrbt_new;
-- 但是不能登录,需要修改密码
sqlplus / as sysdba;
alter user jxzzwx2_new identified by jxzzwx2_new;

-- 压缩功能
compression=all

-- 查询表空间创建脚本
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
2016-06-28 13:593