BulkCopy the corresponding implementation is Oracle's Sql*loader, during which the index unusable is caused, and last_ddl_time is not reflected

Direct cause of index invalidation: when some operations result in ROWID changes to the data, the index is completely invalidated.

When will that cause rowid changes to make the index unuseable or invalid?

General ordinary table in the following 3 cases can make index unusable
1) manual alter index unusable

2) Move "ALTER TABLE move" "ALTER TABLE t02 move tablespace tbs01;" Includes partition operation

3) Sqlldr "Sqlldr (parallel or direct) append" "Sqlldr direct=y + primary KEY Repeat"

Match three options:

1, check dba_objects last_ddl_time, the corresponding time is much earlier than the time period of the problem occurred, the first possibility of excluding

2, check the problem table does not have the partition, also did not do the data movement, the statistic information collection and so on operation, Last_ddl_time also may as the corroboration

3, recall, seems to have not used Sqlldr, very strange, depressed

The table from AWR, Ash, and the time period when the problem occurred has a serious library cache lock wait, querying the historical Performance view dba_hist_active_sess_history, found a large number of library caches Lock waiting sessions are blocked by the same session, and the session is not blocked by others, waiting for the event to be DB file sequential read, not understandable, why is it an index lookup? According to SQL_ID, the SQL script that the current session is executing is not found.

Wait for the next question to reappear, hoping to see the same message, and see what kind of a session is executing on the blocking source.

Sure enough, the next day the problem reappeared, crawling all sessions and currently executing SQL, and immediately found the problem: INSERT/*+ sys_dl_cursor */into "LC0079999". " Jkl_test "(" ID "," C1 "," C2 "," C3 ",) VALUES (Null,null,null,null)

First, this is our data sheet.

Second, it's not like we created the SQL statement manually

Third, check the Sys_dl_cursor keyword, equivalent to Sqlldr (direct=true), reproduce a bulkcopy, sure enough to see this statement

Attention:

The BulkCopy method itself produces the following statements: INSERT/*+ sys_dl_cursor */into "LC0079999". Jkl_test "(" ID "," C1 "," C2 "," C3 ",) VALUES (Null,null,null,null)

/*+ Sys_dl_cursor */is the way SQL loader, which means that bulkcopy needs to be used with caution because he will invalidate the index of the table.

An exclusive lock on the table is executed before the SQL loader is executed: Lock table "LC0079999". " Jkl_test "in EXCLUSIVE MODE NOWAIT

Conclusion: According to the problems that are currently discovered, bulkcopy should be used only for very few concurrent scenarios such as logs, and it is recommended to use bulkcopy carefully

BulkCopy the corresponding implementation is Oracle's Sql*loader, during which the index unusable is caused, and last_ddl_time is not reflected