DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Alter table Space

  1. #1
    Join Date
    Aug 2001
    Posts
    40
    Hi Is there any way that i can aletr all the objects pointing to aperticular table space to pint to another table space,

  2. #2
    Join Date
    Feb 2001
    Posts
    290
    Sorry, couldnt understand the pointing of abjects to a tablespace.

    but to my understanding , may be you are talking about the objects in a tablespace. If so you can go for export all the objects in a tablespace and import to another tablespace , and drop the old one. Or you can do one by one if only few objects in a tablespace.

    if the objects are indexes, simply you can rebuild them in a new tablespace....

    Thanks,
    Madhu

  3. #3
    Join Date
    Aug 2001
    Posts
    40
    i am sorry for not being clear about my issue,
    i have an export fiel in which the table space is test and i want to import it to another schema and the table space in it is name ddifferent,
    for example the table x in expot file is pointing towards test and after importing it i want all the table in table space test to be as test1

    is there any way that i can alter table space of al the tables at once

  4. #4
    Join Date
    Feb 2001
    Posts
    290
    Here is an example which doesnt require EXP/IMP.

    alter TABLE ACCOUNT move
    tablespace users
    storage ( initial 10240 next 10240
    minextents 1 maxextents 121
    pctincrease 50 freelists 1);

    First It will move the table and then it rebuilds the indexes on the table. Have a check on the constraints , they may become
    invalid. Anyway U cn make them enabled.

    PS: try it on some test on a small table , before you go production.

    Thanks,
    Madhu


  5. #5
    Join Date
    Aug 2001
    Posts
    40
    hi madhu,
    probably th escript that u have send me would work for one table but i have to do this for many tables,is there any way that i can do that al of them a time
    thanks

  6. #6
    Join Date
    Feb 2001
    Posts
    290
    I got the following script from metalink for you...

    select decode( segment_type, 'TABLE',
    segment_name, table_name ) order_col1,
    decode( segment_type, 'TABLE', 1, 2 ) order_col2,
    'alter ' || segment_type || ' ' || segment_name ||
    decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
    chr(10) ||
    ' tablespace &1 ' || chr(10) ||
    ' storage ( initial ' || initial_extent || ' next ' ||
    next_extent || chr(10) ||
    ' minextents ' || min_extents || ' maxextents ' ||
    max_extents || chr(10) ||
    ' pctincrease ' || pct_increase || ' freelists ' ||
    freelists || ');'
    from user_segments,
    (select table_name, index_name from user_indexes )
    where segment_type in ( 'TABLE', 'INDEX' )
    and segment_name = index_name (+)
    order by 1, 2
    /

    You can remove the INDEX part in the above script,,, dont forget to edit the script , before you running...

    Thanks,
    Madhu

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