cancel
Showing results for 
Search instead for 
Did you mean: 

Table reorg is failed with error SQL error -12008

Former Member
0 Kudos

Hi Experts,

When am trying to perform the table reorg in oracle database. I am facing an issue and reorg got failed with below error.

BR0280I BRSPACE time stamp: 2015-04-17 17.45.07

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2015-04-17 17.45.08

BR1124I Starting 'online' reorganization of table SAPSR3.BAL_INDX ...

BR0280I BRSPACE time stamp: 2015-04-19 13.29.20

BR0301E SQL error -12008 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPSR3"', ORIG_TABLE => '"BAL_INDX"', INT_TABLE => '"BAL_INDX#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-12008: error in materialized view refresh path

ORA-01555: snapshot too old: rollback segment number 66 with name "_SYSSMU66_4254725801$" too small

ORA-06512: at "SYS.DBMS_REDEFINITION", line 56

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1488

we are performing reorg for expensive table which size is 3.5TB.

If this issue is caused due to undo table space or undo parameters then what would be the undo table space size required to for this table reorg.

Please Help me on this issue.

Regards.

Accepted Solutions (1)

Accepted Solutions (1)

former_member204746
Active Contributor
0 Kudos

increase PSAPUNDO to at least 3 times the size of your table.

good luck.

Former Member
0 Kudos


Hello Eric,

Thanks for suggestion. we plan to increase the undo tablespace.

But increasing 3times the size of table space is much expensive. Is this general recommendation to increase undo tablespace to 3times?

Here initially we performed the cleanups and on post reog has to be performed on same table.

I am going to increase the space, but 3 times would comes around 10TB. i hope this will consume lot of storage space which is unused after reorg.

Is there any alternate solution to overcome this issue?

Kind Regards,

Subba

former_member185954
Active Contributor
0 Kudos

Hello Subba,

Have you checked my query earlier? Can you think about getting rid of the application log before tbreorg ?

Also, if you have no space constraints and can increase the PSAPUNDO, you can easily drop the PSAPUNDO tablespace later and create a smaller PSAPUNDO later after reorg.

The note I suggested has commands to do the same.

Regards,

Siddhesh

former_member204746
Active Contributor
0 Kudos

read Siddesh' answer. I agree with him.. .you can reduce/resize or recreate after you completed... and yes, deleting data out of this space is a MUST!

Answers (8)

Answers (8)

Former Member
0 Kudos

Hello Subba,

As the table is huge it definitely requires an extension of PSAPUNDO tablespace.

go for it, it works!!

Thanks,

Pavan

Former Member
0 Kudos

Hello All,

Thanks for your help...

We perform the online reorg again with adding space to undo tablespace. The reorg got sucessfull.

Undo tablespace is 340GB and increase to around 550 GB.

Undo table space will be dropped and recreated in next maintenance.

Table Bal_indx is too expensive and plan to decrease the retention of Application Log from 180 to 90 days.

Regards,

Subba

Former Member
0 Kudos

Hello Subba,

Perfect .....

Please mark the thread as answered.

Amerjit

former_member207186
Contributor
0 Kudos

Hi,


Thank you for your feedback and for the good news. It is our pleasure to assist you.   


Regards,

Bíborka

Former Member
0 Kudos

Hello Subba,

Is your problem resolved ?

If yes, then please update the forum with the steps taken to resolve the problem so that it will benefit to everyone.

Also, request you to mark the question as Answered

Thanks,

Parin

former_member207186
Contributor
0 Kudos

Hi,


ORA-01555: snapshot too old: rollback segment number 66 with name "_SYSSMU66_4254725801$" too small

You can also refer to SAP Note 3807 - "Error messages regarding rollback and undo segments"  regarding this issue. Also I would like to recommend to check carefully the suggested SAP Notes 185822, 600141, 706478 and update us on the satus of the issue.

You can also check ORA-01555 - causes and solutions for more practical steps to analyse and fix this issue.

Regards,

Bíborka

former_member182657
Active Contributor
0 Kudos

Hi Subba,

Have you checked SAP Note  185822 - ora-1555 - cause and action

which i shared earlier with you,note itself suggesting undo table space calculations & same you could apply at your end.In addition i would suggest to follow mentioned SAP Notes

706478 - Preventing Basis tables from increasing considerably.

195157 - Application Log: Deletion of Logs

as already suggested by Amerjit .

Regards,

Gaurav

Former Member
0 Kudos

Hello,

BAL_IDX = 3.5TB ?? Not a typo ?

I would have expected BALDAT to be a worse table.

How big is your DB ?

In addition to the very useful info provided by Siddhesh and Gaurav, please look at the following OSS note:

706478 - Preventing Basis tables from increasing considerably.

195157 - Application Log: Deletion of Logs


Kind Regards,

Amerjit

former_member185954
Active Contributor
0 Kudos

Hello Subba,

The key question to ask is, why is the table so large.

Can it not be archived, since its only an application log table ?

Are you doing excessive logging ?

Further, since its only a log table, who is using it , is it being displayed regularly and used by business ?

Before starting tablereorgs, consider the following:

Reorganization - SAP Database Guide: Oracle - SAP Library

There are benefits in archiving this table too, if the log is not required to be access frequently.

Archiving Object BC_SBAL - Application Log User Guidelines (BC-SRV-BAL) - SAP Library

Since, you asked about Undo retention, I assume you already have AUM Automatic Undo Manangement setup. In-spite of this if you get errors related to rollback segments, you got limited choice really besides the following.

- Increase Undo Tablespace and Undo Retention - increasing them beyond a certain point may not be helpful or advisable. Check the central note on AUM - 600141 - Oracle9i: Automatic UNDO Management .

although its 9i related, you will see options and details about how oracle manages undo, that might give you some ideas

Good luck.

Regards,

Siddhesh

former_member182657
Active Contributor
0 Kudos

Hi Subba,


we are performing reorg for expensive table which size is 3.5TB.

If this issue is caused due to undo table space or undo parameters then what would be the undo table space size required to for this table reorg.

For this i would suggest you to follow recommendations under point 2 from SAP Note 185822 - ora-1555 - cause and action

Hope this will help you.

Good luck !!

former_member182657
Active Contributor
0 Kudos