oracle imp/exp导入导出数据

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;