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
上一篇: Apache 防盗链的技术小结