Question: MERGE or INSERT /*+ APPEND */ causes INDEX DDL?
Date: Mon, 8 Apr 2013 11:14:03 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8879BC2769D_at_NADCWPMSGCMS10.hca.corpad.net>
I've just begun troubleshooting some various Oracle issues with invalid ROWIDs, ORA-600 [25027], ORA-600 [kcbzfb_1], ORA-08103: object no longer exists errors. The trace file dumped for the 25027 indicates an INSERT /*+ APPEND */ statement and I'm running an analyze table validate structure for all the tables/indexes involved in in the INSERT statement.
While that is running, I cross-referenced DBA_OBJECTS LAST_DDL_TIME and noted that many objects (some of which are involved in this query) are showing INDEX DDL operations on partitioned indexes during the time frame of the errors.
For a direct-load INSERT using the append hint, would that account for the INDEX DDL operations I'm seeing? (I should probably just do a test to confirm) but wanted to poll the list first. (I'll do a test anyway while I'm waiting for a response while I'm thinking about it).
We use many partitioned tables and we have 50-100 concurrent sessions processing distinct ORG_IDs that should be in their own partitions but I do see a PK INDEX showing DDL operations on one of the tables involved in the INSERT statement (the PK index is on a table involved in the SELECT portion of the INSERT).
Regards,
Chris Taylor
Oracle DBA
Parallon IT&S
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 08 2013 - 18:14:03 CEST