重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-1722 During Import [打印本页]

作者: 刘泽宇 时间: 2024-9-29 14:47
标题: ORA-1722 During Import
现象：
When importing a table:

imp <USER>/<PASSWORD>file=<DUMPFILE NAME>.dmp fromuser=<USER 1> touser=<USER 2> tables=<TABLENAME> rows=n buffer=20000000 ignore=y log=<LOGFILE>.log feedback=100000

the import log file contains:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing <USER 1>'s objects into <USER 2>
IMP-00017: following statement failed with ORACLE error 1722:
" ALTER TABLE "<TABLE NAME>" MODIFY ("<COLUMN NAME>" DEFAULT '0,2

原因：
Bug 4038803 IMP-3 ORA-1722 WHEN USING NLS_NUMERIC_CHARACTERS != '.,' SET IN ENVIRONMENT

or

Database corruption

处理方法：
Workaround for the Bug 4038803:
In the source database:

1. Create a reference table:

create table <TABLE NAME>
(
obj#    number,
name    varchar2(30),
default$ varchar2(4000)
);

2. Fill that reference table with the useful information:

declare
  v1 number;
  v2 varchar2(30);
  v3 varchar2(4000)
  CURSOR c_select IS select obj#, name, default$ from col$;

begin
  Open c_select;
  LOOP
Fetch c_select into v1, v2, v3;
exit when c_select%NOTFOUND;
insert into <TABLE NAME> values (v1, v2, v3);
  END LOOP;
  Close c_select;
  commit;
end;
/

3. Query that reference table to check which object and column could have the wrong format:

select distinct O.object_name, D.name, D.default$
from dba_objects O, <TABLE NAME> D
where  D.obj# = O.object_id and
   (default$ like '%,0%' or
      default$ like '%,1%' or
      default$ like '%,2%' or
      default$ like '%,3%' or
      default$ like '%,4%' or
      default$ like '%,5%' or
      default$ like '%,6%' or
      default$ like '%,7%' or
      default$ like '%,8%' or
      default$ like '%,9%' or
      default$ like '%0,%' or
      default$ like '%1,%' or
      default$ like '%2,%' or
      default$ like '%3,%' or
      default$ like '%4,%' or
      default$ like '%5,%' or
      default$ like '%6,%' or
      default$ like '%7,%' or
      default$ like '%8,%' or
      default$ like '%9,%');

4. Change the incorrect default value to a good one:

Alter table <TABLE_NAME> modify column default NEW_DEFAULT_VALUE;

Then you should be able to do your export and import without the issue.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)