on 04-21-2015 4:24 PM
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.
increase PSAPUNDO to at least 3 times the size of your table.
good luck.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
Hello Subba,
As the table is huge it definitely requires an extension of PSAPUNDO tablespace.
go for it, it works!!
Thanks,
Pavan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 !!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hope you followed SAP KBA 1831679 - Problems when doing online reorg of PCL2 table
& Note 741478 - FAQ: Materialized views
Good luck !!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.