Home » RDBMS Server » Server Administration » ORA-01652, ORA-02063 (Solaris 9 - DB 9i and 10g)
ORA-01652, ORA-02063 [message #344468] Fri, 29 August 2008 22:33 Go to next message
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 #344469 is a reply to message #344468] Fri, 29 August 2008 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

02063, 00000, "preceding %s%s from %s%s"
// *Cause: an Oracle error was received from a remote database link.
// *Action: refer to the preceding error message(s)


>Kindly advise how should we prevent such problem happen
Increase the size of TEMP tablespace.
Re: ORA-01652, ORA-02063 [message #344668 is a reply to message #344469] Sun, 31 August 2008 19:59 Go to previous messageGo to next message
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 #344670 is a reply to message #344468] Sun, 31 August 2008 21:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Which db is having temp unable to extend?

Previously asked & answered.

>What can be done in segment level?
I give up. What can be done in segment level.
Re: ORA-01652, ORA-02063 [message #344913 is a reply to message #344468] Mon, 01 September 2008 20:05 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 25046
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

Previous Topic: Privilege information
Next Topic: Superset in characterset
Goto Forum:
  


Current Time: Wed Dec 07 12:55:52 CST 2016

Total time taken to generate the page: 0.08968 seconds