The below script can be used to update the Init trans for tables, paritions and indexes.
#CHANGE initrans for table paritions:
select 'alter table SCOTT.EMP modify partition '|| PARTITION_NAME || ' initrans 100;' from all_tab_partitions where table_name='EMP' and TABLE_OWNER='SCOTT';
#USE THE ABOVE GENERATED CODE AND CHANGE THE VALUE:
#Verify none is left at 100:
select INI_TRANS, Max_trans,table_name, PARTITION_NAME from all_tab_partitions where table_name='EMP' and INI_TRANS<=100;
#Update index partitions:
select 'alter index SCOTT.EMP_INX modify partition '|| PARTITION_NAME || ' initrans 100;' from all_ind_partitions where INDEX_NAME='EMP_INX' and INDEX_OWNER='SCOTT';
#USE THE ABOVE GENERATED CODE AND CHANGE THE VALUE:
#Verify none is left at OLD VALUE:
select INI_TRANS, Max_trans,INDEX_NAME, PARTITION_NAME from all_ind_partitions where INDEX_NAME='EMP_INX' and INI_TRANS<=100;
#CHANGE initrans for table:
Alter table SCOTT.EMP initrans 100;
#CHANGE initrans for indexes:
Alter index SCOTT.emp_inx initrans 100;
Alter index SCOTT.INDNAME initrans 100;
Alter index SCOTT.INDNAME initrans 100;
#CHANGE initrans for table paritions:
select 'alter table SCOTT.EMP modify partition '|| PARTITION_NAME || ' initrans 100;' from all_tab_partitions where table_name='EMP' and TABLE_OWNER='SCOTT';
#USE THE ABOVE GENERATED CODE AND CHANGE THE VALUE:
#Verify none is left at 100:
select INI_TRANS, Max_trans,table_name, PARTITION_NAME from all_tab_partitions where table_name='EMP' and INI_TRANS<=100;
#Update index partitions:
select 'alter index SCOTT.EMP_INX modify partition '|| PARTITION_NAME || ' initrans 100;' from all_ind_partitions where INDEX_NAME='EMP_INX' and INDEX_OWNER='SCOTT';
#USE THE ABOVE GENERATED CODE AND CHANGE THE VALUE:
#Verify none is left at OLD VALUE:
select INI_TRANS, Max_trans,INDEX_NAME, PARTITION_NAME from all_ind_partitions where INDEX_NAME='EMP_INX' and INI_TRANS<=100;
#CHANGE initrans for table:
Alter table SCOTT.EMP initrans 100;
#CHANGE initrans for indexes:
Alter index SCOTT.emp_inx initrans 100;
Alter index SCOTT.INDNAME initrans 100;
Alter index SCOTT.INDNAME initrans 100;
No comments:
Post a Comment