欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle dmp文件导入

程序员文章站 2022-06-11 23:46:42
...

1.确认dmp文件的导出时用户

$~# head -10 /root/Downloads/xypj.dmp |strings

EXPORT:V09.02.00
UCTAIS2         //CTAIS2
RTABLES
2048
Fri Nov 18 9:46:9 2016d:\xypj.dmp
#C#G
#G##
+08:00
BYTE
INTERPRETED

2.确认dmp文件的导出时字符集

SQL> select nls_charset_name(to_number(‘0001’,’xxxx’)) from dual; //0001是dmp文件按16进制打开的第二三字节

//0354 -> ZHS16GBK
//0001 -> US7ASCII
//0369 -> AL32UTF8

SQL> select to_char(nls_charset_id(‘AL32UTF8’), ‘xxxx’) from dual; //也可以反查

3. Oracle创建新的表空间

connect as SYSDBA
create tablespace TS_DAT_SJCK  DATAFILE 'D:\oraclexe\app\oracle\oradata\XE\TPIN.DBF' SIZE 1500m AUTOEXTEND on next 100m maxsize unlimited;
create user myTpin identified by myTpin default tablespace TS_DAT_SJCK;
grant dba to myTpin;

4. Oracle修改数据库的字符集

conn as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE US7ASCII; // 字符集需要修改
SHUTDOWN immediate;
startup;

5. 查看是否修改成功

$~#
select * from nls_database_parameters;
select * from v$nls_parameters where parameter=’NLS_CHARACTERSET’;

6.dmp imp命令

$~#
imp myTpin/[email protected] file=/root/Downloads/xypj.dmp log=/root/Downloads/tables.log ignore=y fromuser=CTAIS2 touser=myTpin;

7.设置NLS_LANG

C:\Windows\system32>
set NLS_LANG=AMERICAN_AMERICA.US7ASCII
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
set NLS_LANG=Simplified Chinese_China.AL32UTF8
//最好的选择是和客户机保持一致,只起提示转换作用

客户机windows的字符集查看

C:\Windows\system32> chcp

活动代码页: 936 -> ZHS16GBK

8.出现乱码时直接看字节内容

select nsrmc, dump(nsrmc,1016) from XY_NSR_XYJB where NSRDZDAH = 610100000000005034