DB13 Check and update optimizer stat error: ORA-01652: unable to extend tem

[Former Member]

Hi SAP Gurus,

When running Check and Update Optimizer Statistics in DB13, an error occurs.

BR0280I BRCONNECT time stamp: 2011-03-10 06.35.52

BR0301E SQL error -1652 at location stats_tab_collect-16

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

BR0886E Checking/collecting statistics failed for table SAPR3.ACCTIT

BR0280I BRCONNECT time stamp: 2011-03-10 06.36.49

BR0850I 3 of 39479 objects processed - 3.522 of 342.781 units done

BR0204I Percentage done: 1.03%, estimated end time: 15:47

Looking at tablespace SYSTEM in DB02, the percent used is 58. Auto-extent feature is OFF. Will turning the auto-extent ON, remove the error?

[audunlea_hansen · Accepted solution]

It seems like Your user (by a mistake) have tablespace SYSTEM as temporary tablespace.

Connect as sysdba in Your database and check which temp tablespaces awaiable, and size of them:

select tablespace_name, sum(bytes)/1024/1024 "Size of TEMP TBS in MB" from dba_temp_files  group by tablespace_name;

Check which users having SYSTEM as tamp-tbs:

select username, temporary_tablespace from dba_users where temporary_tablespace like 'SYSTEM' order by 1;

Change those users to have one of your temp-tablespaces:

alter user &username temporary tablespace &&TempTBS;

It's also a good idea to have autoextend on on Your temp TBS, but remember to set maxsize so you doesn't fill up your disksystem.

Hope this solve Your problems.

Regards

Audun

DBA

[Former Member · Accepted solution]

Hi Audun,

I extended 287MB in tablespace SYSTEM, then re-execute UPDATE STAT. The number of tables check before was 16 and it increased by one after the extension.

I tried to check the which IDs uses SYSTEM as temporary tablespace and found out four IDs. I compared the result to other servers and the result was no one was using SYSTEM as temporary tablespace.

My problem now is whether I should change the temporary tablespace of the four IDs or should I continue adding additional datafile in tablespace SYSTEM?

[Former Member]

Audun and Nick, you are both correct. Thanks.

[Former Member]

Permanent and recommended solution is to change the teporary tablespce of users to actual temp tablespace.

Workaround and non-recommended solution is to extend the tablespace of SYSTEM by adding datafiles.

Regards,

Nick Loy

[Former Member]

Hi,

Is it me or does this not look right !

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

Why temp segment in the system tablespace ?

Never seen this one before but someone might enlighten me.

Mark

[sivakumar_kilari3]

Yes, You keep AUTOEXTEND ON your SYSTEM table space and check your file system.

Siva.