ORa-1555 Creating an Index of a table with 85 partitions
Database version 8.1.7.4
I'am creating an index to a table that has 85 partitions.
The script is failing with Ora-1555 snapshot rollback segment too small.
The perso who made the database made a RB_BIG_2 rollback segment with 1500 m but the statemnt "set transaction use rollback segment RB_BIG_2" was ignored and the creation of the index was using the forst rollback segment namecd RB00 from the tablespace RBS0.
The big rollback segment is created in another tablespace named RBS_BIG2.
How to make - how to force that my creation of the index use this big rollback segment?
CREATE INDEX PRTC_WS.PS_TBL_CDR_START_T
(START_TIME)
LOCAL (
PARTITION P23
LOGGING
TABLESPACE PRTC_WS_CDR_IND_23,
PARTITION P24
LOGGING
TABLESPACE PRTC_WS_CDR_IND_24,
PARTITION P25
LOGGING
TABLESPACE PRTC_WS_CDR_IND_25,
PARTITION P26
LOGGING
TABLESPACE PRTC_WS_CDR_IND_26,
PARTITION P27
LOGGING
TABLESPACE PRTC_WS_CDR_IND_27,
PARTITION P28
LOGGING
TABLESPACE PRTC_WS_CDR_IND_28,
PARTITION P29
LOGGING
TABLESPACE PRTC_WS_CDR_IND_29,
PARTITION P01
LOGGING
TABLESPACE PRTC_WS_CDR_IND_01,
PARTITION P02
LOGGING
TABLESPACE PRTC_WS_CDR_IND_02,
PARTITION P03
LOGGING
TABLESPACE PRTC_WS_CDR_IND_03,
PARTITION P04
LOGGING
TABLESPACE PRTC_WS_CDR_IND_04,
PARTITION P05
LOGGING
TABLESPACE PRTC_WS_CDR_IND_05,
PARTITION P06
LOGGING
TABLESPACE PRTC_WS_CDR_IND_06,
PARTITION P07
LOGGING
TABLESPACE PRTC_WS_CDR_IND_07,
PARTITION P08
LOGGING
TABLESPACE PRTC_WS_CDR_IND_08,
PARTITION P09
LOGGING
TABLESPACE PRTC_WS_CDR_IND_09,
PARTITION P10
LOGGING
TABLESPACE PRTC_WS_CDR_IND_10,
PARTITION P11
LOGGING
TABLESPACE PRTC_WS_CDR_IND_11,
PARTITION P12
LOGGING
TABLESPACE PRTC_WS_CDR_IND_12,
PARTITION P13
LOGGING
TABLESPACE PRTC_WS_CDR_IND_13,
PARTITION P14
LOGGING
TABLESPACE PRTC_WS_CDR_IND_14,
PARTITION P15
LOGGING
TABLESPACE PRTC_WS_CDR_IND_15,
PARTITION P16
LOGGING
TABLESPACE PRTC_WS_CDR_IND_16,
PARTITION P17
LOGGING
TABLESPACE PRTC_WS_CDR_IND_17,
PARTITION P18
LOGGING
TABLESPACE PRTC_WS_CDR_IND_18,
PARTITION P19
LOGGING
TABLESPACE PRTC_WS_CDR_IND_19,
PARTITION P20
LOGGING
TABLESPACE PRTC_WS_CDR_IND_20,
PARTITION P21
LOGGING
TABLESPACE PRTC_WS_CDR_IND_21,
PARTITION P22
LOGGING
TABLESPACE PRTC_WS_CDR_IND_22,
PARTITION P30
LOGGING
TABLESPACE PRTC_WS_CDR_IND_30,
PARTITION P31
LOGGING
TABLESPACE PRTC_WS_CDR_IND_31,
PARTITION P32
LOGGING
TABLESPACE PRTC_WS_CDR_IND_32,
PARTITION P33
LOGGING
TABLESPACE PRTC_WS_CDR_IND_33,
PARTITION P34
LOGGING
TABLESPACE PRTC_WS_CDR_IND_34,
PARTITION P35
LOGGING
TABLESPACE PRTC_WS_CDR_IND_35,
PARTITION P36
LOGGING
TABLESPACE PRTC_WS_CDR_IND_36,
PARTITION P37
LOGGING
TABLESPACE PRTC_WS_CDR_IND_37,
PARTITION P38
LOGGING
TABLESPACE PRTC_WS_CDR_IND_38,
PARTITION P39
LOGGING
TABLESPACE PRTC_WS_CDR_IND_39,
PARTITION P40
LOGGING
TABLESPACE PRTC_WS_CDR_IND_40,
PARTITION P41
LOGGING
TABLESPACE PRTC_WS_CDR_IND_41,
PARTITION P42
LOGGING
TABLESPACE PRTC_WS_CDR_IND_42,
PARTITION P43
LOGGING
TABLESPACE PRTC_WS_CDR_IND_43,
PARTITION P44
LOGGING
TABLESPACE PRTC_WS_CDR_IND_44,
PARTITION P45
LOGGING
TABLESPACE PRTC_WS_CDR_IND_45,
PARTITION P46
LOGGING
TABLESPACE PRTC_WS_CDR_IND_46,
PARTITION P47
LOGGING
TABLESPACE PRTC_WS_CDR_IND_47,
PARTITION P48
LOGGING
TABLESPACE PRTC_WS_CDR_IND_48,
PARTITION P49
LOGGING
TABLESPACE PRTC_WS_CDR_IND_49,
PARTITION P50
LOGGING
TABLESPACE PRTC_WS_CDR_IND_50,
PARTITION P51
LOGGING
TABLESPACE PRTC_WS_CDR_IND_51,
PARTITION P52
LOGGING
TABLESPACE PRTC_WS_CDR_IND_52,
PARTITION P53
LOGGING
TABLESPACE PRTC_WS_CDR_IND_53,
PARTITION P54
LOGGING
TABLESPACE PRTC_WS_CDR_IND_54,
PARTITION P55
LOGGING
TABLESPACE PRTC_WS_CDR_IND_55,
PARTITION P56
LOGGING
TABLESPACE PRTC_WS_CDR_IND_56,
PARTITION P57
LOGGING
TABLESPACE PRTC_WS_CDR_IND_57,
PARTITION P58
LOGGING
TABLESPACE PRTC_WS_CDR_IND_58,
PARTITION P59
LOGGING
TABLESPACE PRTC_WS_CDR_IND_59,
PARTITION P60
LOGGING
TABLESPACE PRTC_WS_CDR_IND_60,
PARTITION P61
LOGGING
TABLESPACE PRTC_WS_CDR_IND_61,
PARTITION P62
LOGGING
TABLESPACE PRTC_WS_CDR_IND_62,
PARTITION P63
LOGGING
TABLESPACE PRTC_WS_CDR_IND_63,
PARTITION P64
LOGGING
TABLESPACE PRTC_WS_CDR_IND_64,
PARTITION P65
LOGGING
TABLESPACE PRTC_WS_CDR_IND_65,
PARTITION P66
LOGGING
TABLESPACE PRTC_WS_CDR_IND_66,
PARTITION P67
LOGGING
TABLESPACE PRTC_WS_CDR_IND_67,
PARTITION P68
LOGGING
TABLESPACE PRTC_WS_CDR_IND_68,
PARTITION P69
LOGGING
TABLESPACE PRTC_WS_CDR_IND_69,
PARTITION P70
LOGGING
TABLESPACE PRTC_WS_CDR_IND_70,
PARTITION P71
LOGGING
TABLESPACE PRTC_WS_CDR_IND_71,
PARTITION P72
LOGGING
TABLESPACE PRTC_WS_CDR_IND_72,
PARTITION P73
LOGGING
TABLESPACE PRTC_WS_CDR_IND_73,
PARTITION P74
LOGGING
TABLESPACE PRTC_WS_CDR_IND_74,
PARTITION P75
LOGGING
TABLESPACE PRTC_WS_CDR_IND_75,
PARTITION P76
LOGGING
TABLESPACE PRTC_WS_CDR_IND_76,
PARTITION P77
LOGGING
TABLESPACE PRTC_WS_CDR_IND_77,
PARTITION P78
LOGGING
TABLESPACE PRTC_WS_CDR_IND_78,
PARTITION P79
LOGGING
TABLESPACE PRTC_WS_CDR_IND_79,
PARTITION P80
LOGGING
TABLESPACE PRTC_WS_CDR_IND_80,
PARTITION P81
LOGGING
TABLESPACE PRTC_WS_CDR_IND_81,
PARTITION P82
LOGGING
TABLESPACE PRTC_WS_CDR_IND_82,
PARTITION P83
LOGGING
TABLESPACE PRTC_WS_CDR_IND_83,
PARTITION P84
LOGGING
TABLESPACE PRTC_WS_CDR_IND_84,
PARTITION P85
LOGGING
TABLESPACE PRTC_WS_CDR_IND_85
)
NOPARALLEL;
Rollback.doc
I'am creating an index to a table that has 85 partitions.
The script is failing with Ora-1555 snapshot rollback segment too small.
The perso who made the database made a RB_BIG_2 rollback segment with 1500 m but the statemnt "set transaction use rollback segment RB_BIG_2" was ignored and the creation of the index was using the forst rollback segment namecd RB00 from the tablespace RBS0.
The big rollback segment is created in another tablespace named RBS_BIG2.
How to make - how to force that my creation of the index use this big rollback segment?
CREATE INDEX PRTC_WS.PS_TBL_CDR_START_T
(START_TIME)
LOCAL (
PARTITION P23
LOGGING
TABLESPACE PRTC_WS_CDR_IND_23,
PARTITION P24
LOGGING
TABLESPACE PRTC_WS_CDR_IND_24,
PARTITION P25
LOGGING
TABLESPACE PRTC_WS_CDR_IND_25,
PARTITION P26
LOGGING
TABLESPACE PRTC_WS_CDR_IND_26,
PARTITION P27
LOGGING
TABLESPACE PRTC_WS_CDR_IND_27,
PARTITION P28
LOGGING
TABLESPACE PRTC_WS_CDR_IND_28,
PARTITION P29
LOGGING
TABLESPACE PRTC_WS_CDR_IND_29,
PARTITION P01
LOGGING
TABLESPACE PRTC_WS_CDR_IND_01,
PARTITION P02
LOGGING
TABLESPACE PRTC_WS_CDR_IND_02,
PARTITION P03
LOGGING
TABLESPACE PRTC_WS_CDR_IND_03,
PARTITION P04
LOGGING
TABLESPACE PRTC_WS_CDR_IND_04,
PARTITION P05
LOGGING
TABLESPACE PRTC_WS_CDR_IND_05,
PARTITION P06
LOGGING
TABLESPACE PRTC_WS_CDR_IND_06,
PARTITION P07
LOGGING
TABLESPACE PRTC_WS_CDR_IND_07,
PARTITION P08
LOGGING
TABLESPACE PRTC_WS_CDR_IND_08,
PARTITION P09
LOGGING
TABLESPACE PRTC_WS_CDR_IND_09,
PARTITION P10
LOGGING
TABLESPACE PRTC_WS_CDR_IND_10,
PARTITION P11
LOGGING
TABLESPACE PRTC_WS_CDR_IND_11,
PARTITION P12
LOGGING
TABLESPACE PRTC_WS_CDR_IND_12,
PARTITION P13
LOGGING
TABLESPACE PRTC_WS_CDR_IND_13,
PARTITION P14
LOGGING
TABLESPACE PRTC_WS_CDR_IND_14,
PARTITION P15
LOGGING
TABLESPACE PRTC_WS_CDR_IND_15,
PARTITION P16
LOGGING
TABLESPACE PRTC_WS_CDR_IND_16,
PARTITION P17
LOGGING
TABLESPACE PRTC_WS_CDR_IND_17,
PARTITION P18
LOGGING
TABLESPACE PRTC_WS_CDR_IND_18,
PARTITION P19
LOGGING
TABLESPACE PRTC_WS_CDR_IND_19,
PARTITION P20
LOGGING
TABLESPACE PRTC_WS_CDR_IND_20,
PARTITION P21
LOGGING
TABLESPACE PRTC_WS_CDR_IND_21,
PARTITION P22
LOGGING
TABLESPACE PRTC_WS_CDR_IND_22,
PARTITION P30
LOGGING
TABLESPACE PRTC_WS_CDR_IND_30,
PARTITION P31
LOGGING
TABLESPACE PRTC_WS_CDR_IND_31,
PARTITION P32
LOGGING
TABLESPACE PRTC_WS_CDR_IND_32,
PARTITION P33
LOGGING
TABLESPACE PRTC_WS_CDR_IND_33,
PARTITION P34
LOGGING
TABLESPACE PRTC_WS_CDR_IND_34,
PARTITION P35
LOGGING
TABLESPACE PRTC_WS_CDR_IND_35,
PARTITION P36
LOGGING
TABLESPACE PRTC_WS_CDR_IND_36,
PARTITION P37
LOGGING
TABLESPACE PRTC_WS_CDR_IND_37,
PARTITION P38
LOGGING
TABLESPACE PRTC_WS_CDR_IND_38,
PARTITION P39
LOGGING
TABLESPACE PRTC_WS_CDR_IND_39,
PARTITION P40
LOGGING
TABLESPACE PRTC_WS_CDR_IND_40,
PARTITION P41
LOGGING
TABLESPACE PRTC_WS_CDR_IND_41,
PARTITION P42
LOGGING
TABLESPACE PRTC_WS_CDR_IND_42,
PARTITION P43
LOGGING
TABLESPACE PRTC_WS_CDR_IND_43,
PARTITION P44
LOGGING
TABLESPACE PRTC_WS_CDR_IND_44,
PARTITION P45
LOGGING
TABLESPACE PRTC_WS_CDR_IND_45,
PARTITION P46
LOGGING
TABLESPACE PRTC_WS_CDR_IND_46,
PARTITION P47
LOGGING
TABLESPACE PRTC_WS_CDR_IND_47,
PARTITION P48
LOGGING
TABLESPACE PRTC_WS_CDR_IND_48,
PARTITION P49
LOGGING
TABLESPACE PRTC_WS_CDR_IND_49,
PARTITION P50
LOGGING
TABLESPACE PRTC_WS_CDR_IND_50,
PARTITION P51
LOGGING
TABLESPACE PRTC_WS_CDR_IND_51,
PARTITION P52
LOGGING
TABLESPACE PRTC_WS_CDR_IND_52,
PARTITION P53
LOGGING
TABLESPACE PRTC_WS_CDR_IND_53,
PARTITION P54
LOGGING
TABLESPACE PRTC_WS_CDR_IND_54,
PARTITION P55
LOGGING
TABLESPACE PRTC_WS_CDR_IND_55,
PARTITION P56
LOGGING
TABLESPACE PRTC_WS_CDR_IND_56,
PARTITION P57
LOGGING
TABLESPACE PRTC_WS_CDR_IND_57,
PARTITION P58
LOGGING
TABLESPACE PRTC_WS_CDR_IND_58,
PARTITION P59
LOGGING
TABLESPACE PRTC_WS_CDR_IND_59,
PARTITION P60
LOGGING
TABLESPACE PRTC_WS_CDR_IND_60,
PARTITION P61
LOGGING
TABLESPACE PRTC_WS_CDR_IND_61,
PARTITION P62
LOGGING
TABLESPACE PRTC_WS_CDR_IND_62,
PARTITION P63
LOGGING
TABLESPACE PRTC_WS_CDR_IND_63,
PARTITION P64
LOGGING
TABLESPACE PRTC_WS_CDR_IND_64,
PARTITION P65
LOGGING
TABLESPACE PRTC_WS_CDR_IND_65,
PARTITION P66
LOGGING
TABLESPACE PRTC_WS_CDR_IND_66,
PARTITION P67
LOGGING
TABLESPACE PRTC_WS_CDR_IND_67,
PARTITION P68
LOGGING
TABLESPACE PRTC_WS_CDR_IND_68,
PARTITION P69
LOGGING
TABLESPACE PRTC_WS_CDR_IND_69,
PARTITION P70
LOGGING
TABLESPACE PRTC_WS_CDR_IND_70,
PARTITION P71
LOGGING
TABLESPACE PRTC_WS_CDR_IND_71,
PARTITION P72
LOGGING
TABLESPACE PRTC_WS_CDR_IND_72,
PARTITION P73
LOGGING
TABLESPACE PRTC_WS_CDR_IND_73,
PARTITION P74
LOGGING
TABLESPACE PRTC_WS_CDR_IND_74,
PARTITION P75
LOGGING
TABLESPACE PRTC_WS_CDR_IND_75,
PARTITION P76
LOGGING
TABLESPACE PRTC_WS_CDR_IND_76,
PARTITION P77
LOGGING
TABLESPACE PRTC_WS_CDR_IND_77,
PARTITION P78
LOGGING
TABLESPACE PRTC_WS_CDR_IND_78,
PARTITION P79
LOGGING
TABLESPACE PRTC_WS_CDR_IND_79,
PARTITION P80
LOGGING
TABLESPACE PRTC_WS_CDR_IND_80,
PARTITION P81
LOGGING
TABLESPACE PRTC_WS_CDR_IND_81,
PARTITION P82
LOGGING
TABLESPACE PRTC_WS_CDR_IND_82,
PARTITION P83
LOGGING
TABLESPACE PRTC_WS_CDR_IND_83,
PARTITION P84
LOGGING
TABLESPACE PRTC_WS_CDR_IND_84,
PARTITION P85
LOGGING
TABLESPACE PRTC_WS_CDR_IND_85
)
NOPARALLEL;
Rollback.doc
David VanZandt
I'm away from my 8i docs, but isn't there syntax to the effect of: alter session set rollback_segment = xxx? And of course, it may be necessary to break your statement into smaller components, ie, where rownum LT or GT some value.
ASKER
Well I double check with my journal and also with a co-worker and she told me that the line "set transaction......" is the correct.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.