Tables move from tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tables move from tablespace

  1. #1
    Join Date
    Aug 2001
    Posts
    16

    Tables move from tablespace

    Hi All,

    I am a new member and new into DBA.

    Is it possible to move a table from one table space to another table space?

    Thanks in advance

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    alter table move tablespace
    works after 9i and locks the entire table for the duration of the operation.
    Not sure but I think the corresponding indexes will be made unisable and must be rebuilt.
    It is possible to be necessary to issue
    alter table enable row movement;
    before issuing alter table move

    Regards

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes you do need re-build indexes, no you dont need to enable row movement

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Bore
    alter table enable row movement;
    Not relevant -- that enables row movement between partitions.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Dave,
    I checked and really in this case row movement is not necessary, however I do not agree that it means enable movement between partitions. It rather means enable rowid change.
    Row movement is for sure necessary when you do segment shring in 10g or when you do flashback table and that has nothing to deal with the partitioning. That's why, since move tablespace leads to rowid changes, I presumed that enable row movement could be necessary

    Best
    Boris

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    row movement has everything to do with partitions, if you update the partition key of a record and it needs to go to a different partition, you will need row movement turned on for that to work

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Bore
    Row movement is for sure necessary when you do segment shring in 10g ...
    Ah ha, quite so. I learned something there. Thanks Boris, now I need an excuse to go and shrink a segment ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Davey23uk (many Dave's here :-) )
    I have not said that row movement has nothing to deal witgh partitioning. Of course it has. What I say is that row movement is ALSO necesary for operations like shrink and flashback, and these operations are permitted and defined on non-partitioned tables as well. So, Slimdave is not wrong here, just his first statement is not fully precise.

    Regards
    Boris

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