We have Oracle 8i(8.1.7) database running on True64 V5.1B. We want to migrate it to Oracle 10g R2. I installed Oracle 10g R2 S/W and did an export and import into Oracle 10g R2 database. The import was successful without any warnings. Auto Segment Space Management in 10g manages PCT_USED and FREELISTS. So this solved FREELISTS problem. But, INITRANS is still same.
1) So, We want to increase the INITRANS parameter for all the TABLES and INDEXES. As we know the default for TABLES is 1 and for INDEXES is 2. If we use ALTER TABLE/INDEX to change the value of INITRANS, it will affect only the NEW BLOCKS (From Oracle Documentation). So, it will not affect or change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS. I would like to know if there is a way to change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS.
2) Is there a way to check these values for all the DATA/INDEX BLOCKS in a SEGMENT(TABLE/INDEX). I mean from some DATA DICTIONARY VIEWS?
1. Existing data blocks can not be changed. Oracle will dynbamically add initrans to the data block if/when it need to do so.
2. Not that I know of at the data block level.
3. As D states rebuild them..
Thanks for all your advises. I understood that I have to rebuild the Segment for changing the INITRANS. So, First I have to change the INITRANS using ALTER TABLE/INDEX. This will change INITRANS for new DATA/INDEX blocks only.
So, I have to export and import to change the INITRANS for all the blocks. Am I right?