oracle imp/exp导入导出数据
1.检查服务端字符集
select userenv('language') from dual;
----
AMERICAN_AMERICA.AL32UTF8
2.若目标服务端字符集不一致需修改
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.
SQL> alter database open;
Database altered.
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
//提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
Database altered.
//重启检查是否更改完成:
SQL> shutdown immediate;
SQL> shartup;
3.导出数据文件
//语法 exp 用户名/密码@实例名 file=文件路径 owner=用户名 rows=n log=文件路径;
//这条命令的意思是导出指定用户的所有对象table、seq、pro...,但是不会导出数据
//Eg:
exp user/pwd@orcl file=/export/export001.dmp owner=user rows=n log=export001.log;
4.导入数据结构
//语法 imp 用户名/密码@实例名 file=文件路径 log=文件路径
//Eg:
imp user/pwd@orcl file=/export/export001.dmp log=import001.log;