How to fix the Oracle error ORA-01409: NOSORT option may not be used; rows are not in ascending order?

In this post, you’ll learn more about the Oracle ErrorORA-01409: NOSORT option may not be used; rows are not in ascending order with the details on why you receive this error and the possible solution to fix it.

Oracle Error Description

ORA-01409: NOSORT option may not be used; rows are not in ascending order

Reason for the Error ORA-01409: NOSORT option may not be used; rows are not in ascending order

Creation of index with NOSORT option when rows were not ascending. For non-unique indexes the rowid is considered part of the index key. Therefore, if you create an index nosort and two of the rows in the table have the same key and are stored in ascending order, but get split accross two extents where the dba of the first block in the second extent is less than the dba of the last block in the first extent, then the create index nosort may fail.

How to fix the Error ORA-01409: NOSORT option may not be used; rows are not in ascending order ?

You can fix this error in Oracle by following the below steps

Create the index without the NOSORT option, or ensure table is stored in one extent.