1

I have a number of tables that use function based indexes (indices if you prefer). These indexes use functions within packages that I have defined. When importing the schema of the user it would appear that the tables get imported first, then the indexes and then the packages.

I say thin because I get errors such as the following;

ORA-39083: Object type INDEX failed to create with error:
ORA-04067: not executed, package body "DEVELOPE.METAPHONE" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPE.METAPHONE"
Failing sql is:
CREATE INDEX "DEVELOPE"."CLAIMS_PATIENT_INDEX_2" ON "DEVELOPE"."CLAIMS_PATIENT" (
"GENDER", "DEVELOPE"."METAPHONE"."GENPRIMKEY"("NAME_FIRSTNAME",3), 
TO_NUMBER(TO_CHAR("DOB",'YYYY')), SUBSTR("ADDR_ZIP",1,3)) PCTFREE 10 INITRANS 2 
MAXTRANS 166  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTEN

Are there parameters within impdp that will resolve this problem? Or another workaround?

If I create the index after the impdp finishes, everything works fine.

neshkeev
  • 6,280
  • 3
  • 26
  • 47
Paul Stearns
  • 856
  • 9
  • 30
  • 1
    This is "Bug 17943479 ORA-39083/ORA-904 from Import with REMAP_SCHEMA for virtual column or function based index on PLSQL function". The workaround is "none". The bug claims to be fixed in 12.2 but I just hit the same problem in that version. It's pretty annoying to hit the same problem 5 years later and there's still no simple fix. – Jon Heller Aug 27 '19 at 17:17

1 Answers1

1

Use the option: content=data_only or you can write your own importer by using DATAPUMP API

You can import your schema with several attempts:

  1. tables and tables' data

  2. functions

  3. indexes

I believe it has to be possible to implement.

neshkeev
  • 6,280
  • 3
  • 26
  • 47
  • content=data_only will import the row data, how do I then create the packages and indexes? – Paul Stearns Jul 31 '14 at 15:32
  • I looked at the DATAPUMP API link briefly. I did not see a way (did I mention briefly) to change the order that the import does things. Did I miss it? – Paul Stearns Jul 31 '14 at 15:41
  • 1
    You can import your schema with several attempts: at first tables and tables' data then functions and at the end indexes. I believe it has to be possible to implement. – neshkeev Jul 31 '14 at 16:46
  • I agree with zaratustra that running in multiple passes is likely the best solution. I've had to do that before to avoid similar issues with indexes and datapump. – Jon Heller Jul 31 '14 at 17:45