| ORA-01652, ORA-02063 [message #344468] |
Fri, 29 August 2008 22:33  |
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
|
Member |
|
|
Following error shows in alert log.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from ABC
Query select using dblink. The uable to extend temp segment is occur in local db (9i) or the remote db (10g)?
Kindly advise how should we prevent such problem happen. Thank you.
Below is the statement.
SELECT distinct
POL.ITEM_DESCRIPTION ITEM,
msi.segment1 ITEM_CODE,
--POL.CANCEL_FLAG,
TO_CHAR(POH.CREATION_DATE, 'YYYYMM') ORDER_DATE,
POH.CURRENCY_CODE CURRENCY_CODE,
POL.UNIT_PRICE UNIT_PRICE,
POH.AUTHORIZATION_STATUS STATUS,
POV.VENDOR_NAME VENDOR_NAME
FROM
apps.PO_HEADERS_ALL@abc POH,
apps.PO_LINES_ALL@abc POL,
apps.PO_LINE_LOCATIONS_ALL@abc PLL,
apps.PO_DISTRIBUTIONS_ALL@abc POD,
apps.PO_VENDORS@abc POV,
apps.HR_ALL_ORGANIZATION_UNITS@abc HAO,
apps.MTL_PARAMETERS@abc MP,
apps.MTL_CATEGORIES_B@abc MCB,
apps.mtl_system_items@abc msi
WHERE
POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HAO.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND POH.AUTHORIZATION_STATUS = 'APPROVED'
AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND (POL.CANCEL_FLAG IS NULL OR POL.CANCEL_FLAG = 'N')
AND POL.CATEGORY_ID = MCB.CATEGORY_ID
and (pol.item_id = msi.inventory_item_id or pol.item_id is null)
and msi.organization_id = '82'
AND MCB.SEGMENT1 IN ('INDIRECT')
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POV.VENDOR_TYPE_LOOKUP_CODE NOT IN ('EMPLOYEE', 'NON-TRADE')
order by POL.ITEM_DESCRIPTION, TO_CHAR(POH.CREATION_DATE, 'YYYYMM')
|
|
|
|
|
|
| Re: ORA-01652, ORA-02063 [message #344668 is a reply to message #344469] |
Sun, 31 August 2008 19:59  |
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
|
Member |
|
|
Thanks for the response.
Question:
Which db is having temp unable to extend?
Other than adding datafile, what is other good pratice to avoid unable to extend segment in temp tablespace?
What can be done in segment level?
Pls advise.
|
|
|
|
|
|
| Re: ORA-01652, ORA-02063 [message #344913 is a reply to message #344468] |
Mon, 01 September 2008 20:05 |
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
|
Member |
|
|
Besides keep adding datafiles, is there any other method we can take to avoid this error?
The error appear in local and remote alert log. Perhaps I didn't see the answer that u had given. Anyway, sorry if I've asking the wrong term.. but it shouldn't reply in this manner.
Thank you.
|
|
|
|
| Re: ORA-01652, ORA-02063 [message #344914 is a reply to message #344468] |
Mon, 01 September 2008 20:22 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
| suiren97 wrote on Fri, 29 August 2008 20:33 |
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from ABC
|
my response was
| Quote: |
02063, 00000, "preceding %s%s from %s%s"
// *Cause: an Oracle error was received from a remote database link.
|
Most folks would conclude the problem was in the REMOTE database "@ABC"
You are free to draw your own conclusion.
You are attempting to pour 10 liters of water into a 5 liter container & want to know how not to spill any water.
Since you continue this thread, please explain why this query is being run from the local database are not run directly inside the "@ABC" database itself.
Why are you pulling all the data from the remote database for filtering on the local database & generating excessive network traffic?
Why do you have 9 tables in the FROM clause, when only 4 out of the 9 tables actually contribute data to the SELECT clause?
Five of the 9 tables should be removed out of the FROM clause;
which only serve to complicate the problem for the optomizer.
[Updated on: Mon, 01 September 2008 20:34] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
