Tablespace reorg failing with error ORA-12008 and ORA-01555

[Former Member]

Hello,

We are reorganizing the tablespace in our PI System by BR tools.

System config is as below:

SAP Netweaver 7.

PI 7

Oracle 10g.

While doing the reorg of tablespace each time it fails on table SXMSCLUP with below error

BR0301E SQL error -12008 at location tab_onl_reorg-20, SQL statement:
'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPPIP"', ORIG_TABLE => '"SXMSCLUP"', INT_TABLE => '"SXMSCLUP#$"',  OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
BR0280I BRSPACE time stamp: 2014-04-30 00.23.01
BR1106E Reorganization of table SAPPIP.SXMSCLUP failed

BR0280I BRSPACE time stamp: 2014-04-30 00.23.01
BR1141I 1 of 1 table processed - 1296545 of 1296545 rows done
BR0204I Percentage done: 100.00%, estimated end time: 0:23

I have taken care of space in new table space i.e :

Original tablespace size : 700 GB

Temporary tablespace created :700 GB.

Table SXMSCLUP size :  approx 480 GB( including table+ LOBSEGMENT+ Index) - reorg fails on this table only.

PSAPUNDO : 80 GB

PSAPTEMP : 12 GB

Any help is appreciated

[ACE-SAP · Accepted solution]

Hello

How many rows in table SXMSCLUP, at Oracle level does it still have a long raw field or was it migrated to LOB ?

Has it ever been cleaned up ?

872388 - Troubleshooting Archiving and Deletion in PI

Regards

[Former Member · Accepted solution]

Hello Kervadec,

Intially it was communicated with us that all the archiving and deletion jobs has been scheduled. But still it seems that it is not deleting the data.

we are proceeding with the sapnote u have provided.

Also we have scheduled the job "RSXMB_DEL_CLUST_TABLE" to delete data from SXMSCLUP and SXMSCLUR.

Any suggestion and tips are appreciated.

[former_member284393]

Hi Ashish,

Please execute "show parameter undo", firstly, you need to check if the value of undo_retention is set to 43200.If not , please set it. Also please ensure the undo tablespace is autoextend and you have enough space on disk.

[Former Member]

Hi Liu,

What was your computation on the undo_retention? what if the table is 3TB in size, how much would the undo table space increase? there is no room for the undo to grow larger, what would be the next option?

Martin

[former_member284393]

Hi Martin,

You can set undo_retention to 43200.  There is no rule to estimate the size of undo tablespace, if you execute a large SQL statement, then more undo space is required.

But remember that, only when undo tablespace is autoextend, the value of undo_retention is valid. If the undo tablespace size is fixed, then Oracle automatically use an optimal value of undo_retention.

Best regards,

Leo

[Former Member]

Hi Ashish/all,

Did we get an answer from this? Please check on sapnote 774858 - XI 3.0: check and correct the cluster tables which talks about SXMSCLUP table still being large after cleanup.

I don't think we need a reorganization for this. Could anybody confirm.

Martin

[Former Member]

Hello Ashish.

What is the size of you Undo tablespace (rollback segment)?

The error "ORA-01555: snapshot too old: rollback segment number  with name "" too small"  occurs when the specific Data are stored in blocks in PSAPUNDO and when this area have no more space, oracle rewrites this blocks and the data is lost. Finally the process end with this error.

If you can, try to increase the PSAPUNDO at least to same size of you biggest table (SXMSCLUP). The oracle will try to store the entire table in UNDO segment to achieve this operation. And the best way is to start in a low workload time.

After reorg you can resize or recreate you Undo segment.

No forget to check notes to Archive the SXMS* tables and update statistic as well.

I hope this helps you.

[former_member182657]

Hi Ashish,

Please refer SAP Notes 741478 - FAQ: Materialized views and  636475 - Secondary Oracle error codes

Hope this will help you.

Thanks,

Gaurav