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

Thread: moving tables . . .

  1. #1
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    2

    Question

    hi all,

    Is there a way to move a table from one tablespace to another?

    thanks in advance.

    cheers,
    junn

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can if you use 8i otherwise you have to more things

    in 8i you just do

    Code:
    alter table byebye move tablespace index
    /

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    When you move a table to a new tablespace, all of its indexes become invalid and you will need to rebuild them.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    2
    thanks for the advise, dknight mentioned about rebuilding indexes, what about views, do I need to rebuild them as well?

    thanks,
    junn

  5. #5
    Join Date
    Oct 2000
    Posts
    467
    If the user has select/update privileges on the new tablespace (in case of updateable views) then there is no problem.
    If the user/query has no access to that tablespace, then he may need to recreate them. Since view is an object, the view is not moved along with the table, especially when the view is referring to other objects as well. You need to create /replace the view too in that case.

    Cheers,
    Vinit

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Just FYI

    Using DBA_OBJECTS, you can find all of the objects with a status other than 'VALID'.

    Indexes for the moved table will be invalid. In addition, any packages, procedures, or functions based on the table will be invalid, but they will be recompiled the first time they are called.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by vinit
    If the user has select/update privileges on the new tablespace (in case of updateable views) then there is no problem.
    If the user/query has no access to that tablespace, then he may need to recreate them. Since view is an object, the view is not moved along with the table, especially when the view is referring to other objects as well. You need to create /replace the view too in that case.
    I'm sorry, the above is all wrong.

    - User can never have any privilege granted on a particular tablespace. Privileges are granted on objects, no matter in which TS those objects reside.
    - You don't have to do anything with views when moving the tables, views are note affected with the MOVE command. Even if they become invalid (I'm not sure this realy happens when moving tables) they will be recompiled automaticaly whene they are referenced for the first time. You certanly don't need to recreate them, as in that case you would have to take care of the grants on those views again!

    [Edited by jmodic on 09-19-2001 at 07:28 AM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2001
    Posts
    134
    Hi
    How I can see this table is in which tablespace ?
    Thanx

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Jani
    Hi
    How I can see this table is in which tablespace ?
    Thanx
    select
    TABLE_NAME, TABLESPACE_NAME
    from
    USER_TABLES
    order by
    TABLESPACE_NAME, TABLE_NAME;

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