DBIF_RSQL_SQL_ERROR

[Former Member]

Hi,

I am running a standard job "GPEPAUK36PL211KL4BEIGGDZGN3730".

But I am getting this dump:

<b>SQL error 1630 occurred when accessing table "COSP "</b>

<b>Database error text........: "ORA-01630: max # extents (2000) reached in temp

segment in tablespace PSAPTEMP#"

Internal call code.........: "[RSQL/FTCH/COSP ]" </b>

This is the info @ the table:

Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)

SAPR3 COSP TABLE PSAPBTABD 2,918,416 364,802 70 300 81,920

So what do u suggest.

Regards,

Mofizur

[Former Member]

Hi,

Please goto DB02 and perform a refresh, then click "Current sizes" button, and check usage of table space PSAPTEMP.

With regards

Sudha

[stefan_koehler]

Hello Mofizur,

http://ora-01630.ora-code.com/

=> Action: If maxextents for the tablespace is less than the the system maximum, you can raise that. Otherwise, raise pctincrease for the tablespace

Regards

Stefan

[Former Member]

Hello Stefan,

Thank for your prompt response.This is my Production system.

Info @ PSAPTEMP:

<b>INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN CONTENTS EXTENT_MANAGEMENT

1,048,576.00 1,048,576.00 1 2,000.00 0 0 PERMANENT DICTIONARY</b>

So now what is ur suggestion.What is the pros & cons of extending the maxextents becoz u know this is Production system.

Regards,

Mofizur

[stefan_koehler]

Hello Mofizur,

at first you have to clarify why you need so much temporary space (for example big sorts,etc.)

If this is in SAP standard and you can not modify it (by selection or coding) - the only change is to increase the maxextents on PSAPTEMP.

Contra:

- You need more space in PSAPTEMP

- Maybe some performance issues in extending / cleaning up temp segments (because of permanent and dictionary managed)

Pros:

- Application is working )

Unfortunately you don't tell us your oracle version, because the temp segment handling depends on some factors..

If you have metalink access - have a look at note #61997.1

Regards

Stefan

[Former Member]

Hello Stefan,

It's Oracle 8.1.7.3.0 on Windows Server 2003 SAP4.6C.

Regards,

Mofizur

[stefan_koehler]

Hello Mofizur,

ok then .. i quote from the metalinknote.

When does SMON coalesce?
    o. SMON wakes itself every 5 minutes and checks for tablespaces with
       default pctincrease != 0.

So as i see in your case PSAPTEMP has pctincrease = 0. So increase the maxentents and your problem will be solved (but you maybe need more space for PSAPTEMP).

Please think of making the PSAPTEMP tablespace to type temporary...

Regards

Stefan

[fidel_vales]

Hi,

First mention that 817 is long time out of support, you know what that means:

1) you should, at least, install the terminal release patch set (8174)

2) better, upgrade to a supported released.

Your temp tablespace is a permanent one, why don't you change to a "temporal" one, you will solve this error ( but you should check the space allocation, what are you doing that needs so much temp space ) take a look at notes 416772 and 659946