Initial Extent sizes on tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Initial Extent sizes on tables

  1. #1
    Join Date
    Aug 2000
    Posts
    236
    I have just taken over a 10GB database and on analyzing a few key tables, I find that the intial extent sizes range from 50-70MB. It is an OLTP application and I think that such big extent sizes are not good.

    How do I go about fixing this problem? Export table and import it into a new table withh appropriate extent sizes? What happens to the associated indexes/views?

    Thanks,
    Nizar.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Big extents, but are the tables huge? EXP/IMP would do the trick. Rebuild the indexes afterwards. Don't worry about the view, only recompile them after the import of the tables.


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what version is it?

  4. #4
    Join Date
    Nov 2001
    Location
    Chennai
    Posts
    22
    Make sure that the privileges assigned on the table to various users are also taken care of while trying to import.

    Cheers


  5. #5
    Join Date
    Aug 2000
    Posts
    236
    Version 8.1.6

    Nizar

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    then you dont need imp/exp, you can use

    create a new tablespace aaaa

    alter table xxx move tablespace aaaa storage(initial 4M next 4M pctincrease 0)

    then mnove back to original tablespace, this is to avoid fragmentate your original tablespace, if itīs LMT then you can dmove the table in same tablespace

  7. #7
    Join Date
    Aug 2000
    Posts
    236
    Pando,

    Thanks for your reply. I will appreciate your help on these questions also. We are planning to bring this database down for 2-3 days for reorg maintenance.

    First:

    1. Will changing from dictionary managed to LMT's have any response in performance?

    2. And can I do this by creating LMT's and importing all the tables from my current dump?

    3. Any watchits or issues to be considered while doing this change?

    Thanks,

    Nizar

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    lmt is faster yes, the space management has almost zero overhead

    you can exp, drop the tbsps and create new tbsps as lmt then import back yes

    well i havent had a problem with lmt yet so..

  9. #9
    Join Date
    Nov 2001
    Location
    Chennai
    Posts
    22
    Are LMTs more advantageous than DD based tablespaces?

    If LMTs are used, are Dr.Codd's principles of RDBMS violated?

    I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

    Any other points to be noted in this regard by me?


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by sundaju
    Are LMTs more advantageous than DD based tablespaces?

    If LMTs are used, are Dr.Codd's principles of RDBMS violated?

    I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

    Any other points to be noted in this regard by me?

    LMT is faster and better than DDMT, period

    There is nothing to do between tablespaces and Coddīs RULE!

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