经过高级工程师确认该表是外部表expdp未能导出来,impdp导入时自然也就报错了。
参考:http://blog.sina.com.cn/s/blog_61c44d780102w343.html
exp user/passwd@IP:1521/testdb parfile=/path/par.file
par.file内容file=/path/exp.dmp
tables=(tab1,tab2,tab3)log=/path/exp.log
imp user/passwd@IP:1521/testdb file=/path/exp.dmp log=imp.log full=y
参考:https://blog.csdn.net/dd215130268/article/details/51001627
exp和imp导出导入过程需要数据库和操作系统的字符集保持一至,否则数据会字符集转换
查询数据select userenv('language') from dual; 操作系统echo $NLS_LANG
SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'NLS_CHARACTERSET';
export NLS_LANG=AMERICAN.AMERICA_ZHS16GBK
参考:https://blog.csdn.net/clm20482/article/details/100226660
修改目标数据库字符集过程startup mount;
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 ZHS16GBK;
shutdown immediate;
startup;
参考:http://blog.itpub.net/9240380/viewspace-1849340/
AL32UTF8是ZHS16GBK的超集,非父子集关系,前者每个中文字符占用3字节后者占用2字节
ALTER DATABASE CHARACTER SET ZHS16GBK;
select parameter,value from v$nls_valid_values where parameter='CHARACTERSET' and VALUE like '%ZH%;
参考:https://blog.csdn.net/u013169075/article/details/73250297
直接拷贝外部文件再创建表的方式导入,先建外部表导出,再建表导入。
create table scott.dept_emp organization external
(
type oracle_datapump
default directory ext
location('dept_dp.data')
)
as
select * from scott.dept;
create table eoda.extbk
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(type oracle_datapump
default directory ext
location('dept_dp.data')
);