ORA-1652: unable to extend temp

[former_member182034]

Dear Oracle Expert,

I am getting below issue ORA-1652: unable to extend temp segment by 128 in tablespace.

while the current status of PSAPTEMP file is

PSAPTEMP    2,000.00    2,000.00    0    NO    2,000.00    2,000.00    0    1    0    0    ONLINE    TEMPORARY    DISABLED

As per sap note: 659946

ORA-01652: This error is caused by a tablespace overflow. As a rule of thumb, PSAPTEMP should be at least twice as large as the largest index. If an overflow occurs despite this size, this is frequently due to external reasons such as incorrect Oracle parameter settings, large-scale parallel processing or unfavorable access paths. See also Note 3155.

the status of top 5 index is

SAPSR3    ACCTCR~0	INDEX    PSAPSR3    4,804.000    259    614,912    0.063
SAPSR3    D010TAB~1	INDEX    PSAPSR3700    3,681.000    60    471,168    1,153.594
SAPSR3    D010TAB~0	INDEX    PSAPSR3700    3,656.000    59    467,968    1,391.953
SAPSR3    BSIS~0	INDEX    PSAPSR3    3,592.000    240    459,776    0.063
SAPSR3    FAGLFLEXA~0	INDEX    PSAPSR3    2,752.000    227    352,256    0.063

now..what do you suggest me that I have to change the mode of autoextend or add a new datafile in PSAPTEMP.

If there is need to increase the size of PSAPTEMP then how much size i have to assign it?

please tell me if you required more information.

Regards,

[Former Member · Accepted solution]

Hi Jamil,

Regarding the information you provided, it will be good enough to resize data file to 10GB. Do not set autoextend option for the PSAPTEMP tablespace.

Please note that this is the initial size. You should monitor the PSAPTEMP utilization, to check whether the size has the enough.

Best regards,

Orkun Gedik

[former_member182034 · Accepted solution]

Thanks respected Orkun,

I can do this with brtools but would you tell me SQL command to resolve the ORA-1652.

while the temp file is:

SQL> select * from v$tempfile;
     FILE# CREATION_CHANGE# CREATION_TIME          TS#     RFILE# STATUS
---------- ---------------- --------------- ---------- ---------- -------
ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
---------------------------------------------------------------------------
         2       1348712915 08-APR-12                3          1 ONLINE
READ WRITE 2097152000     256000   2097152000       8192
G:\ORACLE\PRD\SAPDATA1\TEMP_2\TEMP.DATA1

Regards,

[Former Member · Accepted solution]

Hi my friend,

Please find the statement, below;

SQL>alter database datafile 'G:\ORACLE\PRD\SAPDATA1\TEMP_2\TEMP.DATA1' resize 10000M;

Best regards,

Orkun Gedik

[former_member182034 · Accepted solution]

Dear,

As you know that the current temp file is exist in G:\ORACLE\PRD\SAPDATA1\TEMP_2. May I delete these highlighted file.

what do you say if i move the current file into temp_1 and then delete the remaining folder?

Regards,

[Former Member · Accepted solution]

To be at the safe side, rename the directories. Then, restart the database. After that if the everything is stable, you can drop them safely.

Best regards,

Orkun Gedik