How to copy data from external volume to Oracle

Question

I am trying to copy the parquet files from external volume to Oracle db, but I am facing this issue :

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01502: index 'xxx.yyyy' or partition of such index is in unusable state

,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01502: index 'xxx.yyyy' or partition of such index is in unusable state

,Source=msora28.dll,'

What is the issue here?

Answer 1

I think the index is being marked unusable due to a failed operation or maintenance task.

First, you need to identify which index or index partition is in an unusable state. You can do this by running the following SQL query in your Oracle database:

SELECT index_name, partition_name, status
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT index_name, partition_name, status
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'

Once you have identified the unusable index or index partition, you need to rebuild it. You can do this using the ALTER INDEX statement.

To rebuild an entire index:

ALTER INDEX xxx.yyy REBUILD

To rebuild a specific partition of an index:

ALTER INDEX xxx.yyy REBUILD PARTITION partition_name
After rebuilding the index, verify that the index status is now VALID:
SELECT index_name, partition_name, status
FROM dba_indexes
WHERE index_name = 'yyy'
UNION ALL
SELECT index_name, partition_name, status
FROM dba_ind_partitions
WHERE index_name = 'yyy'

Once the index is rebuilt and its status is VALID, you can retry the data copy operation.