11 Replies Latest reply on Dec 15, 2011 8:26 AM by 667657

Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 9, 2011 11:21 AM

Hi,
we are having 4 dim table & 2 fact tables all tables are joined with PK:Org_ID colunm.When we choose columns from more then one Dim tables then getting following error.
kindly help on this.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 16001] ODBC error state: S1000 code: 1652 message: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1. [nQSError: 16015] SQL statement execution failed. (HY000)

1. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

daan bakboord Dec 9, 2011 12:57 PM (in response to Harshith)

Hi,

You should first check in the logfile which physical query has been fired to the Oracle Database.

Is this the statement you would expect to be fired?

Good Luck,

Daan Bakboord
http://obibb.wordpress.com
Like Show 0 Likes(0)

Actions
2. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 12, 2011 11:37 AM (in response to daan bakboord)

Hi,
I checked the log files but unable to identifies the cause of error.
Like Show 0 Likes(0)

Actions
3. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Robert Angel Dec 12, 2011 3:30 PM (in response to Harshith)

Your error message is typical when you have a query that will return a large amount of data, are you sure your single ORG_ID link is sufficient to produce meaningful results, if you recreated the same query using SQL directly would you get a meaningful result?

regards,

Robert.
Like Show 0 Likes(0)

Actions
4. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 13, 2011 6:03 AM (in response to Robert Angel)

Hi,
more specifically, We are having views called OE_ORDER_LINES_V,OE_ORDER_HEADERS_V , ARFV_CASH_DISTRIBUTIONS, AR_PAYMENT_SCHEDULES_V and one custom Fact table. If generate report with columns from same table then able to generate reports.
If use columns more then one table then getting same error.Please help on this.
Joins:
"FIN"."".""."Dim ARFV_CASH_DISTRIBUTIONS"."ORG_ID" = "FIN"."".""."Sales Fact"."ORG_ID"
"FIN"."".""."Dim OE_ORDER_HEADERS_V"."ORG_ID" = "FIN"."".""."Sales Fact"."ORG_ID"
"FIN"."".""."Dim OE_ORDER_LINES_V"."ORG_ID" = "FIN"."".""."Sales Fact"."ORG_ID"
"FIN"."".""."Dim AR_PAYMENT_SCHEDULES_V"."ORG_ID" = "FIN"."".""."Sales Fact"."ORG_ID"

Edited by: 867932 on Dec 12, 2011 9:47 PM

Edited by: 867932 on Dec 12, 2011 10:00 PM
Like Show 0 Likes(0)

Actions
5. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Yogi1729 Dec 13, 2011 7:11 AM (in response to Harshith)

hi,

increase the table space. and at the same delete the all the cache from the OBIEE.

Thanks,
Like Show 0 Likes(0)

Actions
6. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 13, 2011 9:08 AM (in response to Yogi1729)

Yogi,
Can you please provide the steps to increase TEMP table Space in OBIEE.
Like Show 0 Likes(0)

Actions
7. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Yogi1729 Dec 13, 2011 3:11 PM (in response to Harshith)

hi

will you please look at this. this may be help you how to increase the temp table space.

http://mhabib.wordpress.com/2007/03/31/increase-the-size-of-tablespace/

Thanks,

Yogi
Like Show 0 Likes(0)

Actions
8. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 14, 2011 4:52 AM (in response to Yogi1729)

I already checked the link which you provided me. My question is 'is this possible to do in OBI Env?' If Yes,Please share the inputs.Thank you!
Like Show 0 Likes(0)

Actions
9. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

daan bakboord Dec 14, 2011 7:44 AM (in response to Harshith)

Hi,

There are some views you shouldn't use for querying. These views are optimised by Oracle to retrieve specific records and not for larger volumes. You should try to use the base tables instead. Have a look at etrm.oracle.com. Oracle recommends not to use these views for querying.

Cheers,

Daan Bakboord
http://obibb.wordpress.com
Like Show 0 Likes(0)

Actions
10. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

Harshith Dec 15, 2011 6:32 AM (in response to daan bakboord)

These views are custom views contains less then 4k records.Please advise me.Thank you!
Like Show 0 Likes(0)

Actions
11. Re: Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1

667657 Dec 15, 2011 8:26 AM (in response to Harshith)

You have to ask your DBA to increase the temp table space you cannot do it from OBIEE.
What you can also try is to remove any uncessary sorting from your queries because it's done in the temp segment:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

Regards
Adil

NB: please assign point if it's helpful and close the thread when your question is answered
1 person found this helpful
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.