move table/index from specific tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: move table/index from specific tablespace

  1. #1

    move table/index from specific tablespace

    Hi all,

    I have user default tablespace called "USERS". This tablespace has plenty of spaces with couple of datafiles. I know that oracle can't drop specific datafile instead you have to drop tablespace. What is the best way to do this? Is there a way I can move all tables and indexes to temporary tablespace? After move them all, drop "USERS" tablespace, recreate with proper size and move back all the tables and indexes to new "USERS" tablespace.

    Thank you in advance.

  2. #2
    Hi again,

    Forget couple of thing. Im runing oracle 8i 8.1.7.2 on Solaris 8 sparc.

    thanks again.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    There are a lot of variables to consider here.

    Why do you want to do this?
    What are the space issues you are having?
    How many users are using the tablespace that you want to drop?

    You can not (to my knowledge) assign those objects to a temporary tablespace. I would think about creating a new tablespace that is properly sized, export (FULL if possible), drop the user, create the user and assign the appropriate tablespace, then import the users objects.

    Again you need to be certain of what is in that tablespace before you go down this road. I would look at DBA_SEGMENTS first.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ALTER TABLE MOVE and ALTER INDEX REBUILD are sufficient to move objetcs. NO need to exp/imp.

    Tamil

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