3 Replies Latest reply: Jan 28, 2011 6:16 AM by Lubiez Jean-Valentin RSS

    Drop and Install EXFSYS schema

    Adhika W
      Hi Guys,

      I'm having problem while installing EXFSYS schema after I have dropped it.
      The reason I dropped it is because I got invalid objects in the schema which I cannot compile, so after googling for few hours, I finally decided to recreate the schema.
      Dropping the EXFSYS schema is alright, no issues at all.

      While i ran the catexf.sql, near the end of the script I got these errors:
      create indextype EXPFilter
      *
      ERROR at line 1:
      ORA-04063: type body "EXFSYS.EXPRESSIONINDEXMETHODS" has errors
      ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.EXPRESSIONINDEXMETHODS"
      ORA-06512: at line 4
      
      
      grant execute on expfilter to public
                       *
      ERROR at line 1:
      ORA-04042: procedure, function, package, or package body does not exist
      
      
      
      Statistics associated.
      
      ASSOCIATE STATISTICS WITH INDEXTYPES EXPFilter
      *
      ERROR at line 1:
      ORA-29815: object being associated is not present
      
      
      
      PL/SQL procedure successfully completed.
      
      
      PL/SQL procedure successfully completed.
      
      
      Session altered.
      
      18:23:57 SQL>
      I hope anyone can help me with this because I cannot even find the solution in metalink.

      Regards,
      Adhika
        • 1. Re: Drop and Install EXFSYS schema
          Lubiez Jean-Valentin
          Hello,

          I'm having problem while installing EXFSYS schema after I have dropped it
          How did you DROP it ?

          For EXFSYS, you should use the script catnoexf.sql to deinstall EXFSYS. More over, you 'll have to DROP the Public Synonyms created by "catexf.sql". You'll have more details on the following Note of My Oracle Support:
          How To Install and Uninstall Expression Filter Feature or EXFSYS schema [ID 258618.1]
          I don't know if you have already used this method and if it could solve the problem.


          Hope this help.
          Best regards,
          Jean-Valentin
          • 2. Re: Drop and Install EXFSYS schema
            Adhika W
            Hi Lubiez, thanks for your answer.
            Yes of course I followed the right procedure to drop the EXFSYS.
            I followed that metalink document you mentioned. Still I got that error message.

            Regards,
            Adhika
            • 3. Re: Drop and Install EXFSYS schema
              Lubiez Jean-Valentin
              Hello,


              After removing again the EXFSYS schema, you may open the catexf.sql, and execute step by step all the operations.

              You'll see that the script exftyp.sql is called. You can open this script which create the Type ExpressionIndexMethods and, add a SPOOL statement at the top of the script exftyp.sql so as to generate a log.

              By that way you may see better why the Type ExpressionIndexMethods has some errors.


              Hope this help.
              Best regards,
              Jean-Valentin