change initial extent?????/
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: change initial extent?????/

  1. #1
    Join Date
    Jan 2001
    Posts
    157

    Exclamation

    I have a table that has been designed wrongly.
    The initial extent is 5m and next extent is 10m,etc of which both the initial and the next
    extents have been used so far. I want to make the initial and next to
    be 10m each.
    (NOTE: I know that you can not alter a table to change the initial extent).
    But my question is if I export the table, truncate it, then import it
    with the idea of making all the extents one. Can I at this point change the initial
    extent after I have truncated the table since there will be no data in it
    before importing it back.

    These are the steps I'm thinking of using:
    1.Export the table
    2.Truncate the table
    3.Alter the table to change the initial and next extents
    4.Import the table

    P.S. I have always done it like this to change the next extent and it
    worked great but I have not done it to change the initial extent before.


    Any ideas "guys"?

  2. #2
    Join Date
    Feb 2001
    Posts
    114
    cant we create a new table from the old table and specify storage parameters for the new table while creating that?

    CREATE TABLE new(

    oldcol1, oldcol2, oldcol3, deptno )

    TABLESPACE LARGE_TS

    STORAGE (INITIAL 500M NEXT 5M PCTINCREASE 0)

    AS

    SELECT oldcol1, oldcol2, oldcol3, deptno

    FROM old

    let me know

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, this will not work. In step 2., when you truncate the table, it will preserve the storage parameters and there is no way you can change the initial extent. Basicaly you have two options:

    a) If you are on 8i, you can simply reebuild the table with:

    ALTER TABLE blahblah ..... MOVE ....;

    With this clause you can specify new storage parameters (including INITIAL) and you can rebuild online. No need to export, truncate, import....

    b) If you are on pre-8i, you would have to export, drop the table, create the same table table with new storage parameters and import the data. Howevere, you would also have to take care of all constraints, grants, indexes etc... as you will loose all this with DROP command.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    1.Export the table with compress=n
    2.drop the table
    3.Create the table to change the initial and next extents
    4.Import the table with ignore=y
    Reddy,Sam

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Didn't want to confuse anyone, my previous post was ment as a response directly to clinton's question, not to yenni143's sugestion...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2001
    Posts
    13

    alter table move

    I will give to you another sugestion : if the oracle database in runing in 8i,
    you can perform the command alter table move tablespace.
    I did this test:

    create table test (a varchar2(30))
    tablespace td_users
    pctfree 10
    pctused 40
    storage (initial 10k next 10k pctincrease 0);


    ALTER TABLE TEST MOVE TABLESPACE TD_TOOLS
    PCTFREE 5
    pctused 50
    storage (initial 20k next 50k pctincrease 10);

    The results were okay!

    After that, you can move the table back to td_users.

    Good luck!
    Valentin

  7. #7
    Join Date
    Jan 2001
    Posts
    157
    thanks for all the responses

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