This content has been marked as final. Show 4 replies
Hi Noel,1 person found this helpful
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.
. . . .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)?
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)
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
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'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.
Hi Noel,1 person found this helpful
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.