Re: Question: MERGE or INSERT /*+ APPEND */ causes INDEX DDL?

From: Peter Hitchman <pjhoraclel_at_gmail.com>
Date: Tue, 9 Apr 2013 11:33:47 +0100
Message-ID: <CAPMSPxMPvB3eNh+_e1EbQOkZQ2Davcamx2=QKYhx3aZQ798fUg_at_mail.gmail.com>

Hi Chris,
I do not think that a direct load gets reported as a DDL, I always assumed it is just another type of DML.
Do you use interval partitions? Partitions getting created updates the last_ddl_time and so does enabling a constraint. Regards
Pete

On 8 April 2013 17:14, <Christopher.Taylor2_at_parallon.net> wrote:

> 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-l
>
>
>

-- 
Regards

Pete


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 09 2013 - 12:33:47 CEST