Home » RDBMS Server » Server Administration » ORA-01652: unable to extend temp segment by 16 in tablespace TEMP
| ORA-01652: unable to extend temp segment by 16 in tablespace TEMP [message #7484] |
Tue, 17 June 2003 04:00  |
Sarah Harvey
Messages: 7
Registered: April 2003
|
Junior Member |
|
|
I wonder if anyone can help me, the following SQl Query has started producing Oracle Error ORA-01652.
The Problem started when I had to change the code behind table B, this was originally only taking data from the Inventory_Transaction table as follows: -
(SELECT Sku_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - dstamp) + SIGN(dstamp - SYSDATE + '&2')) , +1, DECODE(code, 'Receipt' , update_qty , 0),0)) AS REC
FROM Inventory_transaction
GROUP BY sku_id) B
However, I then since discovered that each Sku could have more than one Config_id (see table H in main SQL query) and thus the report needs to breakdown table B accordingly. This is not made easy given that the config_id is NOT held in the Inventory_transaction table. I have therefore tried to join the Inventory table (where the config_id is stored) to the Inventory_transaction table by the Tag_id,Client and Sku and by then selecting the config_id I had hoped that I could join B.config_id to H.config_id.
Am sure this sounds terribly confusing but I suspect my code is now so wrong processing is taking up all the space available in the temp table. The problem is not helped either by the fact that I will also need to break down tables D,E,F and G also by the config_id!!
SELECT DISTINCT S.product_group,
S.Sku_id,
S.Description,
NVL(A.CRR - B.REC + C.SHIP - F.ADJ,0) as OpeningStock,
NVL(CEIL(((A.CRR - B.REC + C.SHIP - F.ADJ) / H.STOR)),0) as StoragePallets,
NVL(D.GDSIN,0) as GoodsIn,
NVL(CEIL(D.GDSIN / H.STOR),0) as PalletsIn,
NVL(E.OUT,0) as GoodsOut,
NVL(CEIL(E.OUT / H.STOR),0) as PalletsOut,
NVL(F.ADJ,0) as Adjustments,
NVL(G.CLS,0) as ClosingStock,
NVL(G.CLS,0) as CurrentStock
FROM SKU S
, (SELECT Sku_id,config_id,
SUM(Qty_On_Hand) AS CRR
FROM Inventory
GROUP BY sku_id, config_id) A
,(SELECT IT.Sku_id, I.tag_id, I.config_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - IT.dstamp) + SIGN(IT.dstamp - SYSDATE + '&2')) , +1, DECODE(IT.code, 'Receipt' , IT.update_qty , 0),0)) AS REC
FROM Inventory_transaction IT, Inventory I
WHERE I.Sku_id = IT.Sku_id
AND I.client_id = IT.client_id
AND I.tag_id = IT.tag_id
GROUP BY IT.sku_id, I.Tag_id, I.Config_id) B
,(SELECT Sku_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - dstamp) + SIGN(dstamp - SYSDATE + '&2')) , +1, DECODE(code, 'Shipment' , update_qty , 0),0)) AS SHIP
FROM Inventory_transaction
GROUP BY sku_id) C
,(SELECT Sku_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - dstamp) + SIGN(dstamp - SYSDATE + '&2')) , +1, DECODE(code, 'Receipt' , update_qty , 0),0)) AS GDSIN
FROM Inventory_transaction
GROUP BY sku_id) D
,(SELECT sku_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - dstamp) + SIGN(dstamp - SYSDATE + '&2')) , +1, DECODE(code, 'Shipment' , update_qty , 0),0)) AS OUT
FROM Inventory_transaction
GROUP BY sku_id) E
,(SELECT sku_id,
SUM(DECODE(SIGN(SIGN(SYSDATE - dstamp) + SIGN(dstamp - SYSDATE + '&2')) , +1, DECODE(code, 'Adjustment' , update_qty , 0),0)) AS ADJ
FROM Inventory_transaction
GROUP BY sku_id) F
,(SELECT sku_id,
SUM(Qty_On_Hand) AS CLS
FROM Inventory
GROUP BY sku_id) G
,(SELECT sku_id,config_id,
SUBSTR(CONFIG_ID, GREATEST(INSTR(CONFIG_ID, 'L'),INSTR(CONFIG_ID, 'L',1,2),INSTR(CONFIG_ID, 'L',1,3)) + 1, (GREATEST
(INSTR(CONFIG_ID, 'C'),INSTR(CONFIG_ID, 'C',1,2),INSTR(CONFIG_ID, 'C',1,3)) - GREATEST(INSTR(CONFIG_ID,'L'),INSTR(CONFIG_ID,'L',1,2),INSTR(CONFIG_ID,'L',1,3)) - 1)) AS STOR
FROM Sku_sku_config
GROUP BY sku_id, config_id) H
Where S.Sku_id = A.Sku_id
AND S.sku_id = B.Sku_id
AND S.sku_id = C.sku_id
AND S.sku_id = D.sku_id
AND S.sku_id = E.sku_id
AND S.sku_id = F.sku_id
AND S.sku_id = G.sku_id
AND S.sku_id = H.sku_id
AND A.config_id = H.config_id
AND B.config_id = H.config_id
AND (('&1' IS NULL) OR (S.Client_id = '&1'))
GROUP BY Product_group, S.sku_id, S.description,H.config_id,A.config_id, crr, rec, ship, gdsin, out, adj, cls, stor
ORDER BY Product_group
|
|
|
|
| Re: ORA-01652: unable to extend temp segment by 16 in tablespace TEMP [message #7496 is a reply to message #7484] |
Tue, 17 June 2003 19:40  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It may just be that your TEMP tablespace is way too small. You might try, or have your DBA try, adding a datafile to the TEMP tablespace, as indicated in the complete text of the error message below. When I encountered this problem once before, I found that fixed it for me. Just glancing at your query, although lengthy, it doesn't look like anything Oracle shouldn't be able to handle.
ORA-01652 unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temporary segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 04:41:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
