ORA-14185 error resolution instance

This morning, the middleware service was congested in the production service, and the query results of a history table were found to be very slow. Further, the hash partition index of the table was invalid, it is in the "UNUSABLE" status and the index is rebuilt.

The problem occurs when "alter index index_name rebuild partition PA_1 parallel 32 nologging" is re-created. The following error is returned:

ERROR at line 1:
ORA-14185: incorrect physical attribute specified for this index partition
The error message is as follows:
[Oracle @ node1 ~] $ Oerr ora 1, 14185
14185,000 00, "incorrect physical attribute specified for this index partition"
// * Cause: unexpected option was encountered while parsing physical attributes
// Of a local index partition; valid options for Range or Composite Range
// Partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, PCTUSED,
// LOGGING and TABLESPACE; but only TABLESPACE may be specified for Hash partitions
// Store in () is also disallowed for all but Composite Range partitions
// * Action: remove invalid option (s) from the list of physical attributes
// Of an index partition

The problem is very clear: Because the index specified on the table is a global hash partition index with eight subpartitions, it cannot be used in combination with the logging and nologging syntax during rebuild. This is also true. The same error is reported in the test environment 11gR2.

This situation is now recorded. Please note that.

Note: Create a hash partition and use the hash function to scatter a column to evenly distribute data. This function is generally used to balance I/O. However, it is not easy to manage data, hash partitions cannot be DROP, SPLIT, or MERGE partitions.