4 Replies Latest reply: Sep 27, 2010 2:15 PM by Ben Speckhard-Oracle RSS

    BeginDDL fails on versioned table with function based index

    NoelKhan
      All,

      . . . .[ *Problem* ] Within a DDL session, I successfully added a function based index ( to_char("createtime", 'yyyy-mm') ) to a versioned spatial table. I am now unable to begin a subsequent DDL session. Is there a fix or workaround?

      . . . .[ *Error* ]
      SQL> exec dbms_wm.BeginDDL('parcel_polygon');
      BEGIN dbms_wm.BeginDDL('parcel_polygon'); END;
      
      *
      ERROR at line 1:
      ORA-00904: "CREATETIME": invalid identifier
      ORA-06512: at "SYS.OWM_DDL_PKG", line 3200
      ORA-06512: at "SYS.LT", line 11857
      ORA-06512: at line 1
      . . . .[ *Environment* ] Windows 2003 Server, Oracle 10g R2. OWM_VERSION = 10.2.0.1.0. User is granted: DBA, WM_ADMIN_ROLE, CONNECT and RESOURCE. Both table and index are owned by the same user.

      . . . .[ *Documentation* ] Oracle's documentation states that BeginDDL creates skeletons of objects related to the versioned table such as: indexes, triggers and constraints. [Link | http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14253/long_ref.htm#sthref429 ]. The problem seems to occur at this step. Using OEM to get details on the offending index, I noticed that that indexed column name was within quotes. From experience, I've seen that quotes are sometimes part of a column's name, but that's not the case here.

      UPDATE
      . . . .[References] This post is similar to Creating indexes (ORA-00904: "DELSTATUS": invalid identifier). The AlterVersionedTable procedure does not have an option to drop an index. Is there an undocumented mode?

      Thanking you,
      Noel

      Edited by: Noel Khan on Sep 22, 2010 10:28 AM
        • 1. Re: BeginDDL fails on versioned table with function based index
          Ben Speckhard-Oracle
          Hi Noel,

          How was the index added to the table? The LTS table does not contain the createtime column or any other non-user defined column, so I do not see how it would have been possible to add it during a previous DDL session.  Perhaps the index was added directly to the LT table instead (which is not supported)?

          In order to drop the index you can use the DDL option of AlterVersionedTable and specify the drop sql statement directly. However, this functionality is not available as of 10.2.0.1, so you would need to upgrade to the latest OWM version.

          Regards,
          Ben
          • 2. Re: BeginDDL fails on versioned table with function based index
            NoelKhan
            Ben,

            . . . .Thank you for the quick response, as always. It's really appreciated.

            . . . .Regarding how that index got on there: I specified the LT table's CreateTime column. After additional testing, the DDL session appears to have been irrelevant.

            . . . .My understanding was that DDL sessions are used to inform OWM of changes to a versioned tables OR objects associated to versioned tables. So if the DDL session was irrelevant, then what relationship was created between the index and versioned table and, moreover, how can that relationship be broken (even if temporarily)?


            Thanking You,
            Noel
            • 3. Re: BeginDDL fails on versioned table with function based index
              NoelKhan
              This is a bandaid solution:

              I took my own advice and broke the relationship between the versioned table and the index (on %_LT.CreateTime) by temporarily changing SYS.Obj$.Obj# for that index. After that, I was able to create a DDL session, exec DDL commands and commit the changes. (And then I reverted the Obj#, of course)
              SQL> exec dbms_wm.BeginDDL('parcel_polygon');
              
              PL/SQL procedure successfully completed.
              
              SQL> DROP TRIGGER oclis.trg_AgrPrcl4Rowe;
              
              Trigger dropped.
              
              SQL> exec dbms_wm.CommitDDL('parcel_polygon');
              
              PL/SQL procedure successfully completed.
              I must note that the problem was not an fbi, but (as Ben pointed out) the problem was caused by indexing an OWM-created column

              ----------------------

              I'm just testing another method to eliminate or workaround the offending index. Here, I'm using dbms_wm.Export and dbms_wm.Import to make a copy of the versioned table's content only. If this works, then I can DisableVersioning on the original table, drop it, and rename the copy = original-table. I'll post an update if it pans out.
              • 4. Re: BeginDDL fails on versioned table with function based index
                Ben Speckhard-Oracle
                Hi Noel,

                DDL sessions are needed due to the changes during EnableVersioning that are done to the table. Since Workspace Manager adds a number of metadata columns, transforms the indexes and triggers, etc, we cannot allow DDL directly on the LT table.  As a result, we have a DDL session that creates a skeleton LTS table that resembles the original table. All changes are then done to that table by the user, and commitDDL then determines the change(s) to the table and the appropriate action to take on the LT table.  Sometimes we apply the change directly to LT, other times not. For example, a trigger added to the LTS table would become a procedure, and implemented within the instead of triggers that are created.  It never would exist as a trigger on LT.

                As a result of all of this, we do not support changes directly to the LT table as the user might not always know how to appropriately apply the change.  In your case, when an index was created directly on LT, Workspace Manager was not aware of it, and as a result an error occurred when trying to create the _LTS table during beginDDL.

                There are typically system triggers in place to prevent changes to the _LT table.  But, in the case of create DDL statements, the database does not provide enough context for us to know which dependent object the new object(index in this case) is being created on, and so we are unable to prevent it.  However, drop and alter DDL do provide enough context, which is why attempting to drop the index that was just created would result in an error.  This anomaly is fixed in later versions by parsing the DDL, and prevent the creation from ever taking place.   That is also why we added support for a DDL option during AlterVersionedTable.

                Regards,
                Ben