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.