cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-1652: unable to extend temp segment by 128 in tablespace for PSAPTEMP

former_member403242
Participant
0 Kudos

We got ORA-1652 for PSAPTEMP tablespace

Enviroment: Oracle 10.2.0.2

But in DB02, psaptemp 's free space is 100%

The max index /table size is 1.4G/2.9G

Even we add PSAPTEMP to 10G, still get same error. The error show every hour.

Any suggestion for this issue?

Thanks a lot.

Accepted Solutions (0)

Answers (4)

Answers (4)

lbreddemann
Active Contributor
0 Kudos

Hi Amy,

I think that in this case it's not about making your PSAPTEMP larger.

The right question to ask here has to be: Why is so much temporary space required?

What SQL statements causes this?

Typically statements that use HASH or SORT/MERGE operations are 'good suspects' for this.

Check your shared cursor cache for those kind of statements and figure out whether the expected cardinality of the Join-steps are reasonable and correct.

It happens quite a lot that e.g. BW-queries that use HASH joins are expected to hold only few rows eat up GBs of temp. space just because a few 1000000 rows had had to be stored.

To prevent this from happening current CBO statistics and correct db parameter setup is most often sufficient, especially if you're using the current CBO merge fix.

If you cannot perform this kind of analysis yourself, then you may want to open a support message.

(be sure to reference to this thread so that the whole discussion about setting up PSAPTEMP correctly is not reiterated again).

regards,

Lars

former_member403242
Participant
0 Kudos

Hi, Lars,

Thanks for your reply.

I do check sql statements before I add datafiles for PSAPTEMP tablespace. This error show every hour at dedicated time. So I think it should be background job which cause this problem. When I trace it with ST05, I got nothing. And I don't found such 'HASH' or 'SORT' sql in shared cursor cache.

The system is Ecc6.0.

lbreddemann
Active Contributor
0 Kudos

> I do check sql statements before I add datafiles for PSAPTEMP tablespace. This error show every hour at dedicated time. So I think it should be background job which cause this problem. When I trace it with ST05, I got nothing. And I don't found such 'HASH' or 'SORT' sql in shared cursor cache.

Well, actually to find those statements you would have to look into the execution plans for each statement in the shared cursor cache.

HASH and SORT aren't part of the sql-text, so you don't find them with the build-in DBACockpit monitors.

What I would do would be to - as you know the timeframe the problem is occuring - check the ASH-Tables for statements that:

a) ran during this timeframe

and

b) used single- or multipass temp-actions (since only those do use temp space).

As already written you may also just open a support message for this.

But before that make double sure that the db parameters are as recommended.

regards,

Lars

former_member220071
Active Participant
0 Kudos

Hi Amy ,

Try ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Abhijeet

former_member403242
Participant
0 Kudos

Hi,

For PSAPTEMP tablespace we have 6 datafiles.

All these datafiles are availble in Oracle ( No error found till now).

former_member220071
Active Participant
0 Kudos

Hi Amy ,

Try ALTER TABLESPACE oldname RENAME TO newname.

Abhijeet

former_member403242
Participant
0 Kudos

Why? Any reason to rename datafile of PSAPTEMP?

former_member220071
Active Participant
former_member403242
Participant
0 Kudos

Sorry. I don't get you. This just introduce how to alter name of a tablespace. My question is what's reason for alter tablespace name? Because this name, then Oracle show error message of 1652??

Former Member
0 Kudos

Hi

Check this thread may help you [Link|;

Regards

Uday

Former Member
0 Kudos

Hello Amy

548221 - Temporary Files are created as sparse files

thanks

Bhudev

former_member403242
Participant
0 Kudos

Hi, Bhudev

Thank you for response.

Forget to mention that we run Oracle at Windows system, Not Unix.

Former Member
0 Kudos

Hi Amy

then I think you to do some reorganization

Note 825653 - Oracle: Common misconceptions

659946

and also check the settings for the PSAPTEMP tablespace as pr Notes 107257 and 164925

Bhduev

former_member403242
Participant
0 Kudos

Hi, Bhduev

All notes which you mention here we all checked. They not case which we met.

I think the defination for PSAPTEMP is correct.

Former Member
0 Kudos

Hello Amy

Refer to Note 3155 and 659946,they might be of help

Also when does this error occurs.does it occur when a particular transaction is run or something ir does it occur randomly

Rohit

Former Member
0 Kudos

may be you can check re-creating the psaptemp and reassign the temp file