Application users reported as the job is failing with the error “ORA-01427: single-row subquery returns more than one row“
Observations:
- ORA-01427: single-row subquery returns more than one row
- ORA-26026: unique index BG.SKU_DIMENSION_PK initially in an unusable state
Requested the logs from the application to review the job failure
Severity: ERROR
Timestamp: 10/11/2021 7:58:30 AM
Node: ORA-X101
Thread: SQL_1_1_1
Process ID: 9896324
Message Code: CMN_1022
Message: Database driver error...
CMN_1022 [UPDATE PRODUCT_DIMENSION SET (PRODUCT_NAME, WEB_DESIGNER_NAME, CMOS_CATALOG_ID, CMOS_ITEM, DEPT_CODE, SEO_CANONICAL_URL
……..
WHERE PRODUCT_DIMENSION.PRODUCT_KEY = PRODUCT_DIMENSION_MERGE_TABLE.PRODUCT_KEY)
ORA-12801: error signaled in parallel query server P003, instance plploda0node0:MISCDB11 (1)
ORA-01427: single-row subquery returns more than one row
Database driver error...
Function Name : executeDirect
SQL Stmt : UPDATE PRODUCT_DIMENSION SET (PRODUCT_NAME, WEB_DESIGNER_NAME, CMOS_CATALOG_ID, CMOS_ITEM, DEPT_CODE, SEO_CANONICAL_URL,
PRODUCT_DIMENSION_MERGE_TABLE.PRODUCT_KEY)
Oracle Fatal Error
Database driver error...
Function Name : ExecuteDirect
Oracle Fatal Error
Severity: ERROR
Timestamp: 10/12/2021 10:41:22 AM
Node: ORA-X101
Thread: WRITER_1_*_1
Process ID: 21889322
Message Code: WRT_8229
Message: Database errors occurred:
ORA-26026: unique index BG.SKU_DIMENSION_PK initially in unusable state
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO SKU_DIMENSION(SKU_KEY,SKU_ID,CODE_UPC,CMOS_SKU,HEX_CODE,EXP_SHIP_DATE,IN_STOCK,PUR?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
As part of the test trial, several jobs are run to measure the consistency of the migration. In some cases, a few jobs failed due to application issues. Then the same jobs ran multiple times, and hence application exception occurred with the two above Oracle database errors.
ORA-01427: single-row subquery returns more than one row
DBA looked into the issue and confirmed as the issue belongs to the data issue. I tried to simulate the same issue and was able to produce it. Here the source column is expecting one row. Instead, the sub-query returns multiple rows. Using the = operator, only one row/value will be accepted.
As per the error, the reasons could be:
- Duplicate Data
- Sub Query returning more than one row/value
In our case, the issue is with the duplicate data.
Performed some basic test on DBA Tables
SQL> select count(*) from dba_objects where owner = (select username from dba_users);
select count(*) from dba_objects where owner = (select username from dba_users)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
SQL>
ORA-26026: unique index BG.SKU_DIMENSION_PK initially in unusable state
This issue is similar to the above error, but the error occurred at the dependent object Index. When there are duplicate rows. The application was aborted when the job was running with the above error. DBA tried to rebuild the Index, failing with the same issue as the application error.
SQL> alter index BG.SKU_DIMENSION_PK rebuild online;
alter index BG.SKU_DIMENSION_PK rebuild online
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
If we observe the error, it mentions that the dependent table of this index has duplicate data. But is index cannot accept that data because the INDEX was created as “UNIQUE INDEX” on the primary key.
This issue is again from the data because the application jobs ran multiple times, probably without clearing.
Fix:
- Drop the source Table from where it is loading the data
- Truncate the target table where the data is loading into
- Reimport the table from the source database to get the new data.
- Perform the gathered statistics of the tables.
