ORA-1652: unable to extend temp segment by 2560 in tablespace

[Former Member]

Hi SAP Basis Guys,

While Checking in alert log we are getting an error frequently.

ORA-1652: unable to extend temp segment by 2560 in tablespace .

Currently Our total DB size is 156 GB, PSAPTEMP is 22 GB size already allocated.

Any one suggest whether I need to increase the space for the Table space.

[former_member213250]

Hello Sankar

Just one more point as your are getting PSAPTEMP space errors,

Jus check what is the size of the biggest index on your DB.

A general recommendation for PSAPTEMP should be twice the size of largest index on your DB.

Based on you DB size 156 GB I don;t think your laargest index will be bigger than 10GB but jus a point to check.

Hope it helps.

BR.

Venkat

[Former Member]

Hi,

Refer the Notes - 3155 and 825653

Regards,

Ram

[lbreddemann]

Hi Ram,

sorry, but you're missing out on the core point here.

This is not about space management of the temp tablespace.

PSAPTEMP is large enough in most cases.

This is about the reason for the overly large temp-space usage.

Adding temp-space won't fix anything here.

regards,

Lars

[stefan_koehler]

Hello Lars,

Adding temp-space won't fix anything here.

.. well it all depends on how much you can add 😛 .... just kidding

Regards

Stefan

[lbreddemann]

Hi there,

usually (that is, if you didn't undersize your PSAPTEMP) this error is due to a bad execution plan for a SQL query.

Some operation like HASH, SORT, GROUP, MERGE JOIN CARTESIAN etc. is using up far more space then expected.

A very good approach to get rid of this quickly is to perform the basic DBA tasks properly:

- make sure all parameters are set as recommended

- make sure to have installed a reasonable current Oracle patch (early 2010 is already quite old!)

- make sure that none of the tables are excluded from the statistics update (except the ones with delivered statistics) and that the statistic gathering job is scheduled at least once a week

In far more than 95% of all cases I had to work on in SAP Support, these simple steps solved the issue (even without further detailed analysis).

So, if you're clever and want to save a lot of time, don't miss out on these steps and check whether the issue persists after implementing all of them.

regards,

Lars

[Former Member]

Hi Mark,

Thanks for your reply.

In alert log, It shows the tablespace for PSAPTEMP.

[Former Member]

Hi,

it would be a good idea to identify the SQL statement with the highest tempspace usage.

Maybe this SQL statement is flawed (e.g. due to a cartesian product) so that no size

of PSAPTEMP could ever satisfy the SQL statement. Try this SQL command in sqlplus:

set long 2000
set pagesize 50000
select h.sql_id, t.sql_text, h.temp_space_allocated
from dba_hist_active_sess_history h, dba_hist_sqltext t
where h.sql_id=t.sql_id
and   h.temp_space_allocated=(select max(temp_space_allocated) from dba_hist_active_sess_history);

Does the message in the alertlog really specify PSAPTEMP as the affected tablespace?

ORA-1652 could also happen on other tablespaces.

Regards,

Mark