9 Replies Latest reply: Apr 19, 2010 1:39 PM by user2555615 RSS

    Error message

    577989
      Hi everybody,
      Does anyone have idea what this error message means?

      "SQL Error: ORA-04043: object WM$DSP_UDT_607 does not exist ORA-06512: at "WMSYS.LT", line 12225"

      Or WM$DSP_UDT.... I have see it for first time...

      I have google for it, but nothing found....


      This error happens when I tried to add a new column to version enabled table.

      Thanks for any inputs.
        • 1. Re: Error message
          577989
          I found that this procedures created under wmsys once table becomes version enabled. They are "wm$dsp_udt_****_io" and "wm$dsp_udt_****"....At least now is known the error message.

          Looks like they get lost during import of version enabled database.
          • 2. Re: Error message
            Ben Speckhard-Oracle
            Hi,

            The wm$dsp procedures are created when a user defined trigger exists on the table. Were there any error messages in the import log related to the WMSYS schema? If an error did occur, it is possible that the procedures were not properly recreated by the procedural action that is used at the end of the import. If that is the case, they would need to be regenerated.

            Regards,
            Ben
            • 3. Re: Error message
              577989
              I got this error at the end of import which might be related to the problem:

              Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
              ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
              ORA-00001: unique constraint (WMSYS.WM$CONSTRAINTS_TABLE_PK) violated
              Failing sql is:
              BEGIN
              declare
              compile_exception EXCEPTION;
              PRAGMA EXCEPTION_INIT(compile_exception, -24344);
              begin
              if (system.wm$_check_install) then
              return ;
              end if ;

              execute immediate 'alter package wmsys.ltadm compile';
              execute immediate 'alt


              How I can regenerate those procedures, are there any APIs or commands to do that?


              Thanks a lot, Ben!
              • 4. Re: Error message
                Ben Speckhard-Oracle
                Hi,

                Yes, that is the block of code that is used to regenerate the procedures. I am not sure what would cause the ORA-00001 error. Were there any existing versioned table or workspaces(other than LIVE) on the database that was imported into? If not, I would suggest to run the following query:

                SQL> select owner, table_name from all_wm_versioned_tables where state!='VERSIONED' ;

                You would then be able to use dbms_wm.RecoverMigratingTable on any of these tables. Also, the target and source databases need to have the same version of Workspace Manager installed. Can you confirm whether or not this was the case?

                Regards,
                Ben
                • 5. Re: Error message
                  577989
                  Hi,

                  I have run the query and it returns nothing. The version of OWM in source and destination are identical.

                  I have the environment where it was imported OWM enabled dump many time and it was successful. The last time i did, it gives me this error. I have performed the following procedures:
                  1. Removed all workspace and savepoints from database
                  2. Removed user cascade
                  3. Uninstall OWM
                  4. Install OWM and upgrate it to 10.2.0.4.4 version
                  5. Perform import.

                  The same procedures were successful few times ago. Btw, I have performed successful import on brand new instance with no errors.

                  Regards,
                  Dima
                  • 6. Re: Error message
                    Ben Speckhard-Oracle
                    Well, in that case, something else is causing the procedures to not be created. Since you are able to successfully import into some databases but not this particular one, I would suggest to file a SR if you continue to have issues with this.

                    Regards,
                    Ben
                    • 7. Re: Error message
                      user2555615
                      Was this resolved? I get the same error on import (impdp):

                      ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
                      ORA-00001: unique constraint (WMSYS.WM$CONSTRAINTS_TABLE_PK) violated
                      Failing sql is:
                      BEGIN
                      declare
                      compile_exception EXCEPTION;
                      PRAGMA EXCEPTION_INIT(compile_exception, -24344);
                      begin
                      if (system.wm$_check_install) then
                      return ;
                      end if ;

                      execute immediate 'alter package sys.ltadm compile';
                      execute immediate 'alter




                      My OWM versions match for the dump file and the instance (10.2.0.3.1). I un-install and re-install OWM 10.2.0.3.1, so that I make sure it's empty.

                      Thanks!
                      • 8. Re: Error message
                        Ben Speckhard-Oracle
                        Hi,

                        I would really need to see the import log to know what could possibly be happening. I would suggest to file a SR if you still have the log.

                        You could also upgrade to the latest version of OWM for your database to see if it was fixed. Without knowing exactly what caused the error, I can't say for sure whether or not this would fix it.

                        Regards,
                        Ben
                        • 9. Re: Error message
                          user2555615
                          I have upgraded to OWM 10.2.0.4.4. However, where I get the dump files from, it's still at 10.2.0.3.1, so I have to match the OWM versions to import, and then upgrade to 10.2.0.4.4. The SYS constraints on the LT tables and in WMSYS.WM$CONSTRAINTS_TABLE are not the same names. I'm assuming that is because of this error. I have to manually update the WMSYS.WM$CONSTRAINTS_TABLE with the correct constraint names.