Third Party Stock Count Import Batch Job Fails With 'ORA-01652: unable to extend temp segment by 128 in tablespace TEMP' (Doc ID 2810334.1)

Last updated on MAY 25, 2023

Applies to:

Symptoms

When attempting to process the 3rd party stock count authorization for the entire store with large volume of items, the process fails with the following errors:

ERROR
--------

ERROR-9 Time: 1/20/21 9:39 PM Type: SimServerException Message: Error staging message! [Inbound: false, MessageType: InvAdjustCre, BusinessId: null] Root Cause: Error executing query statement.
oracle.retail.sim.common.core.SimServerException: Error executing query statement.
at oracle.retail.sim.server.dataaccess.BaseOracleDao.querySingle(Unknown Source)
at oracle.retail.sim.server.dataaccess.BaseOracleDao.querySingle(Unknown Source)
at oracle.retail.sim.server.dataaccess.BaseOracleDao.queryForLong(Unknown Source)
at oracle.retail.sim.server.dataaccess.BaseOracleDao.selectNextId(Unknown Source)
at oracle.retail.sim.server.dataaccess.daoimpl.MpsStagedMessageOracleDao.selectNextDocumentId(Unknown Source)
at oracle.retail.sim.server.dataaccess.daoimpl.MpsStagedMessageOracleDao.insert(Unknown Source)
at oracle.retail.sim.server.dataaccess.daoimpl.MpsStagedMessageOracleDao$Proxy$_$$_WeldClientProxy.insert(Unknown Source)
at oracle.retail.sim.service.mps.SimMessageStageHandler.handleOperation(Unknown Source)
at oracle.retail.sim.service.mps.SimMessageStageHandler.handleOperation(Unknown Source)
at oracle.retail.sim.service.core.BaseServiceHandler.execute(Unknown Source)
at oracle.retail.sim.service.mps.MpsServiceImpl.stageSimMessages(Unknown Source)
at oracle.retail.sim.service.mps.MpsServiceImpl$Proxy$_$$_WeldClientProxy.stageSimMessages(Unknown Source)
at oracle.retail.sim.server.integration.stager.SimMessageStager.stageMessages(Unknown Source)
at oracle.retail.sim.server.integration.stager.SimMessageStager.execute(Unknown Source)
at oracle.retail.sim.service.stockcount.MarkStockCountChildAsApprovedCommand.doExecute(Unknown Source)
at oracle.retail.sim.service.core.ServerCommand.execute(Unknown Source)
at oracle.retail.sim.service.stockcount.MarkStockCountChildAsApprovedHandler.handleOperation(Unknown Source)
at oracle.retail.sim.service.stockcount.MarkStockCountChildAsApprovedHandler.handleOperation(Unknown Source)

:
:

2021-01-21 01:35:02,362 ERROR [[ACTIVE] ExecuteThread: '40' for queue: 'weblogic.kernel.Default (self-tuning)'][o.r.s.s.d.d.StockCountItemOracleDao] Error executing query statement: select ITEM_ID, STORE_ID, ITEM_TYPE, DEPARTMENT_ID, CLASS_ID, SUBCLASS_ID, LONG_DESCRIPTION, SHORT_DESCRIPTION, SELLABLE, RFID, DEFAULT_CASE_SIZE, UNIT_OF_MEASURE, UIN_REQUIRED, QUANTITY_TOTAL from STOCK_ITEM_V WHERE STOCK_ITEM_V.STORE_ID = ? AND STOCK_ITEM_V.ITEM_ID IN ( SELECT STOCK_COUNT_LINE_ITEM.ITEM_ID FROM STOCK_COUNT_LINE_ITEM WHERE STOCK_COUNT_LINE_ITEM.STOCK_COUNT_ID = ? AND STOCK_COUNT_LINE_ITEM.STOCK_COUNT_CHILD_ID = ?)
java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Steps to Reproduce:

Scenario 1:

Unit Type - Stock Count

Prerequisites: Set MPS Work Type > InvAdjust Outbound = No

1. Create a PG for Unit Stock Count for store 6 - All Items - Auto authorize ON
2. Schedule the PG created. Unit count will be scheduled for same day.
3. Run the Generate Unit Stock Count job to generate the stock counts via Job Admin > Adhoc Job Screen
4. Log into SOCS UI
5. Open the stock count generated and take the initial snapshot.
6. Upload the Third-party stock count file to the SFTP incoming location.
7. Execute Third Party Stock Count Import job from Adhoc Jobs Screen to process the data file.


Scenario 2:

Unit Stock Count - Auto-Authorize = No


1. Create a PG for Unit Stock Count for store 6 - All Items - Auto-Authorize = No
2. Schedule the PG created. Unit count will be scheduled for same day.
3. Run the Generate Unit Stock Count job to generate the stock counts via Job Admin > Adhoc Job Screen
4. Log into SOCS UI
5. Open the stock count generated and take the initial snapshot.
6. Upload the Third-party stock count file to the sftp incoming location.
7. Execute Third Party Stock Count Import job from Adhoc Jobs Screen to process the data file.
8. Job completed.
9. EICS UI > Operations Views > Stock Counts - Ready to Authorize
10. Select child stock counts
11. Confirm Child
12. Severe System Error occurred. Please contact system administrator.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.