Oracle imp failure, anyone an idea what went wrong here?

Hello,

I'm 'imp'orting a table from oracle 8.1.7 to oracle 9.2

I'm getting these errors, what I want to know is what went wrong and what could I do to prevent it next time.

this is the exp param file:

USERID=system/*****@livelink.test
FILE=test.dmp
LOG=testexp.log
BUFFER=32768
 full=yes
rows=yes
consistent=yes


and this the imp param file:
USERID=system/*****@livelink.test
FILE=test.dmp
LOG=testimp.log
BUFFER=32768
fromuser=peterpan
touser=peterpan


 CREATE TABLE "PETERPAN"."DTREE"
   (      "OWNERID" NUMBER(10,0) NOT NULL ENABLE,
      "PARENTID" NUMBER(10,0) NOT NULL ENABLE,
      "DATAID" NUMBER(10,0) NOT NULL ENABLE,
      "NAME" VARCHAR2(300) NOT NULL ENABLE,
      "ORIGINOWNERID" NUMBER(10,0) NOT NULL ENABLE,
      "ORIGINDATAID" NUMBER(10,0) NOT NULL ENABLE,
      "USERID" NUMBER(10,0) NOT NULL ENABLE,
      "GROUPID" NUMBER(10,0) NOT NULL ENABLE,
      "UPERMISSIONS" NUMBER(10,0) NOT NULL ENABLE,
      "GPERMISSIONS" NUMBER(10,0) NOT NULL ENABLE,
      "WPERMISSIONS" NUMBER(10,0) NOT NULL ENABLE,
      "SPERMISSIONS" NUMBER(10,0) NOT NULL ENABLE,
      "ACLCOUNT" NUMBER(10,0) NOT NULL ENABLE,
      "PERMID" NUMBER(10,0),
      "DATATYPE" NUMBER(10,0),
      "CREATEDBY" NUMBER(10,0),
      "CREATEDATE" DATE,
      "MODIFYDATE" DATE,
      "MAXVERS" NUMBER(10,0),
      "RESERVED" NUMBER(10,0),
      "RESERVEDBY" NUMBER(10,0),
      "RESERVEDDATE" DATE,
      "VERSIONNUM" NUMBER(10,0),
      "DCOMMENT" VARCHAR2(2000),
      "DCATEGORY" VARCHAR2(255),
      "SUBTYPE" NUMBER(10,0),
      "EXATT1" VARCHAR2(255),
      "EXATT2" VARCHAR2(255),
      "ORDERING" NUMBER(10,0),
      "MAJOR" NUMBER(10,0),
      "MINOR" NUMBER(10,0),
      "RELEASEREF" NUMBER(10,0),
      "CHILDCOUNT" NUMBER(10,0),
      "ASSIGNEDTO" NUMBER(10,0),
      "DATEASSIGNED" DATE,
      "DATEEFFECTIVE" DATE,
      "DATEEXPIRATION" DATE,
      "DATEDUE" DATE,
      "DATESTARTED" DATE,
      "DATECOMPLETED" DATE,
      "STATUS" NUMBER(10,0),
      "PRIORITY" NUMBER(10,0),
      "GIF" VARCHAR2(255),
      "EXTENDEDDATA" LONG,
      "CATALOG" NUMBER(10,0),
      "CACHEEXPIRATION" NUMBER(10,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 11714560 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "LIVELINK"
 


IMP-00017: following statement failed with ORACLE error 6550:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3C4B150D66'; SREC.M"
 "AXVAL := 'C31B4A25'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
 "MS_STATS.NUMARRAY(-268088,267336); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);"
 " SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"DTREE"','"OWNERID"', NUL"
 "L ,NULL,NULL,2039,,000490436488474742,0,srec,4,0); END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 319:
PLS-00103: Encountered the symbol "," when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "," to continue.

IMP-00017: following statement failed with ORACLE error 6550:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3C4C300F66'; SREC.M"
 "AXVAL := 'C31B4B33'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
 "MS_STATS.NUMARRAY(-255386,267450); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);"
 " SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"DTREE"','"PARENTID"', NU"
 "LL ,NULL,NULL,1123,,000890471950133571,0,srec,4,0); END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 320:
PLS-00103: Encountered the symbol "," when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "," to continue.

Any idea?

Tolomir

LVL 27
TolomirAdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohammadzahidCommented:
I think you are getting error because of ,,

Please see this line 1123,,000890471950133571,0,srec,4,0); END;"

and line

2039,,000490436488474742,0,srec,4,0); END;"



vishal68Commented:
Hi

This is a known bug in Oracle Export for version less than 8.1.7.3. The solution is to either do the export without statistics i.e. use STATISTICS=none with the exp command or if taking export again is not a possibility, then import without statistics. In your case the imp command would look like the following

USERID=system/*****@livelink.test
FILE=test.dmp
LOG=testimp.log
STATISTICS=none
BUFFER=32768
fromuser=peterpan
touser=peterpan

You can analyze the tables after the import or if you want statistics to be collected with the import itself, you can use STATISTICS=recalculate in the imp command.

HTH
Vishal

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TolomirAdministratorAuthor Commented:
Thanks for your help, my problem is I cannot pinpoint the offending line, line 1 is fine so far. Nothing special.

Any idea what imp does with  

DBMS_STATS.SET_COLUMN_STATS(NULL,'"DTREE"','"PARENTID"', NULL ,NULL,NULL,1123,,000890471950133571,0,srec,4,0);

since this doesn't seem to be a normal data record but something else?!?



TolomirAdministratorAuthor Commented:
@vishal68 : Thank you gonna check it...
TolomirAdministratorAuthor Commented:
Cool!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.