Modify INITRANS for existing BLOCKS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Modify INITRANS for existing BLOCKS

  1. #1
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103

    Smile Modify INITRANS for existing BLOCKS

    Hai.
    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?

    3) Is there a way to change


    regards,
    Dileep Tallam.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    rebuild the segment

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    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..

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Starting 9i, Oracle automatically adds 2 INITRANS for table also even if the create table say 1.

    Do you need more than 2?

  5. #5
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai.
    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?


    regards,
    Dileep Tallam.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    No need to do exp/imp.

    Use ALTER TABLE ..MOVE

  7. #7
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai.
    If I have to modify INITRANS for all the TABLES/INDEXES
    (complete SCHEMA) then I think export and import is a simple method. Or Still you prefer ALTER TABLE MOVE/ALTER INDEX REBUILD?

    regards,
    Dileep Tallam.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width