How to resize table initial size?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How to resize table initial size?

Hybrid View

  1. #1
    Join Date
    Jun 2003
    Posts
    2

    How to resize table initial size?

    Can someone help me please?
    I have a table with lots of extents. How can I resize the table's initial size so all data will be held in the first extent?
    Thanks

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    There are a couple of ways you can do this:

    You can use CREATE TABLE AS ... SELECT .....

    or you can export the data, recreate the table, import the data

    Why do you want to move the data to a single extent?

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by jovery
    or you can export the data, recreate the table, import the data
    or you can export the data with compress=y, drop the table and import the data
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: How to resize table initial size?

    Originally posted by ayeleta
    I have a table with lots of extents.
    Check the size of this table in MB. If it is > 5M and < 160M put it on a tablespace with 5M extents.

    On the otherhand, if it is > 160M put it on a tbs with 160M extents.

    HTH.

  5. #5
    Join Date
    Nov 2000
    Posts
    14
    Hi,

    There are many ways as mentioned by two of our mates. But the question is Why ? Yes, Export and import does it and it has been a traditional way and this tool was made to this job . It is for a different purpose.

    You may want to look at storage parameters for the tables and sort them if you fear you may hit max extents. Its best to go for LMT with uniform extents and forget about the number of extents . It does affect the performance. Even if all the data is in one extent its still all over the disk and not contiguous .

    HTH
    Vish.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by TomazZ
    or you can export the data with compress=y, drop the table and import the data
    I think you are missing one important step here.


    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Question

    Originally posted by Mr.Hanky
    I think you are missing one important step here.
    Shoot!
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Originally posted by TomazZ
    or you can export the data with compress=y, drop the table and import the data
    It's not really adviable to use the compress=y unless you are sure that the data content of the table is using all of the space currently allocated.

    If you have a table which has allocated 200Mb of extents yet the data contained within the table is only 10Mb (due to deletes, etc) then the compress=y will build a table with a single 200Mb extent rather than a single 10Mb extent.

    IMHO it is better to establish the storage requirement first.

    REgards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Yes, I see, good point!
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Mar 2003
    Posts
    2
    Simply use an 'alter table move...' and specify a new storage clause, including a new intial extent. You do not have to move the table to a new tablespace - if you do not specify a tablespace it will be moved to a new extent in the same tablespace. This will preserve your constraints, indexes, and permissions.

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