Unable to extend temp segment by 128 in tablespace :ORA-01652

[Former Member]

Hi all,

Once after DB refresh we are facing few issues in our server.

Work: Company code deletion process.

So we have a test server with a system copy from a production server with same sid and everything but different

Host name.

We are now in 2nd test cycle.

2nd cycle began with DB refresh from prod server.

Now we are facing issue with psaptemp tablespace.

Developers are running rsanaora std program , in sm37 it shows finished after 2hours but in sm21 it shows

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

At first we had 10GB PSAPTEMP after getting this error we extended to 22GB and now also getting same error.

Kb usage and percentage usage are in '0' only.

Table-CKIS

size of this table-143GB

table maxextends - unlimited and

CKIS~0 - unlimited and also checked by setting it with 1600.

Job of this std program is to re-build the indexes.

What will be the route cause for these issues.

I have a doubt about post DB refresh activities.

but done all activities.

Is that any issue raise with post DB activities?

In SM21 it shows its a basis issue.

We are checking it from many ways.

Thanks in Advance for kings,

Kutty

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

[Former Member]

thanks for all

[JPReyes]

So you have done a refresh and you are having this issue while rebuilding indexes?.. Can you please clarify...

Also, you can run stats directly using brconnect..

Regards

Juan

[Former Member]

Hi juan,

Backup restore method using tape

[Former Member]

HI juan,

We found one issue...

After DB refresh we added the data file in psaptemp as it doesnt have after DB restore and

after we faced same issue so we just dropped tablespace and recreated psaptemp with 10GB in the same path as it

is in prod.

But we hope its the only issue.

it seems it is not recognizing temp data files.

correct me if i went wrong...

kutty

[Former Member]

Hi,

There is some problem in header.

It seems there is no temp datafiles in oracle but it shows its there.

Definitely there will be an issue with header.

I dont know how to carry this issue with header.

Thanks,

Kutty

[JPReyes]

I had that problem before... as its the temp tablespace just recreate it with BRTools and it will be fine.

You can get good info at Note 659946 - FAQ: Temporary tablespaces

Regards

Juan

[Former Member]

I had gone through 659946 this note and even 683075,659946,600513.

But even after recreating i am facing same issue.

Dont know what to do now.

kutty

[JPReyes]

When you go to DB02 can you see the PSAPTEMP datafile?

Regards

Juan

[Former Member]

Hi juan,

Yes , in db02 i can view psaptemp datafiles but only problem is with oracle.

In Oracle psaptemp is not there it seems..

Kindly check below thread, which i posted in oracle.

Regards,

kutty

[Former Member]

Hi Juan,

Your answer was very helpful for my issue but i couldnt fix that issue as PSAPTEMP has not

updated in control file as well as in oracle header.

As its a Quality system i am restoring again now.

The process is going on now.

FYI,

My environment:

Same SID,OS,DB but different Hostname.

I have post DB refresh activity in my mind but kindly refresh me what are the post activity should take place

from your point of view so that issue should not arise again.

As you had this same issue before definitely it will be helpful for me to go-ahead for a secure process.

BR,

Kutty

[Former Member]

Hi,

Maybe I am wrong to analyze the issue but can you please look the following SAP note..

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

Regards

[Former Member]

hi,

None of the notes helping to sort out my issue.

Thats why posting for a help hand.

BR,

Kutty

[Former Member]

Hi,

Thanks for your feedback. Can you please check this note..

Note 825653 - Oracle: Common misconceptions

Just check the solution number " 45 "

" If the error "ORA-01652: unable to extend temp segment ... in tablespace PSAPTEMP" occurs, the table space PSAPTEMP must be increased.

In many cases, increasing PSAPTEMP is not a suitable means of avoiding ORA-01652 errors. Instead, you should always check whether the triggering SQL statement executes unnecessarily large sortings or hash operations that cause a high PSAPTEMP load. Only when the performance of the SQL statement is optimal and cannot be improved further, must PSAPTEMP be increased.'

Hope this will helpful to you.

Regards

[Former Member]

Hi,

First thanks for your time.!!!

My developer runs RSANAORA program.

Its purpose is to re-building the indexes, actually i have recreated psaptemp and even extended to 30GB but

no use in these things.

recently i have applied oracle interim opatch 10.2.0.2.0 so dont know it may be the issue.

so need to look little more deeper in this issue.

BR,

Kutty

[Former Member]

Hi Bhuban,

Can i have your point of view in DB refresh post steps?

Give me full detailed steps.

I need to have checklist to find where i went wrong.

BR,

Kutty

[JPReyes]

After a system copy theres no need to rebuild indexes, indexes already exist. The only reason to rebuild indexes is due to a change on the structure of a table. After a system copy its good to update stats but if done via regular system copy procedure stats are updated as part of the process.

recently i have applied oracle interim opatch 10.2.0.2.0 so dont know it may be the issue.

Well, what do you mean by recently?... 10.2.0.1 is not supported, did you upgrade from 9i?

Regards

Juan

[Former Member]

Hi,

Sure..Please see the link below.. One .doc file is there for the details step of DB refresh. Hope this will help you..

<removed_by_moderator>

Regards

Edited by: Juan Reyes on Aug 8, 2011 12:55 PM

[Former Member]

Hi,

Thanks for your time..

Indexes re-build is for another purpose, for company code deletion process.

Oracle interim opatch, i didnt do any upgrade

BR,

Kutty

[Former Member]

Hi,

And How to update stats here and whats the necessary for that?

BR,

Kutty

[venkata_emandi]

Hello,

Use DB20 to update Stats, updating status will improve the performance on the tables.

Thanks

venkata

[Former Member]

Hi Juan an all,

I have fixed the issue by updating the PSAPTEMP in control file by creating psaptemp using

BRTOOLS , Before that i have recreated control file.

Now everything is going fine.

I am now re-building all the largest Index in my Database.

Main monitoring will be with the index that we wants to re-building, we need to monitor the

growth of the tablespace of particular index and need to keep temp twice the size of

largest index.

If anyone facing this same issue you can very well post your thread as open.

Once again thanks for all and Juan....

BR,

Kutty