Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Unanswered: Moving indexes into their own tablespace

    By default, tables and indexes are stored in users01.dbf for Oracle 10g.

    I would like to create another tablespace (say users02.dbf) and move all the indexes onto the new tablespace. How can I do this?

    Thanks in advance
    System: Oracle 10g on Fedora Core 1

  2. #2
    Join Date
    Oct 2004
    Posts
    145
    Two methods.

    1. Drop the index and rebuild with tablespace parameter
    2. Use command to rebuild
    ALTER INDEX index_name REBUILD TABLESPACE USERS02
    /

    Be careful in distinquising data files and tablespaces. What you listed above is a datafile name users01.dbf corresponding tablespace name should be USERS. Ensure you create tablespace for index (eg. USERS_IDX with datafile users02.dbf, usersidx01.dbf, etc)

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you got Enterprise edition you can stick ONLINE at the end so that it allows normal DML to continue.

    Alan

  4. #4
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    I guess I'll have to run the following command for every index in user_indexes:

    Code:
    ALTER INDEX index_name REBUILD TABLESPACE USERS_IDX
    Would the command deallocated space in my USERS tablespace? So that the size of the USERS tablespace would be much smaller, since it wouldn't have the indexes.
    System: Oracle 10g on Fedora Core 1

  5. #5
    Join Date
    Oct 2004
    Posts
    145
    Yes it will free up the space however your tablespace maybe fragmented.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You could also stick COMPUTE STATISTICS on the end and generate some stats for very little overhead.

    Out of interest, why move the indexes? There was some discussion here recently about whether it's worth placing them in separate tablespaces.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by unicef2k
    I guess I'll have to run the following command for every index in user_indexes:

    Code:
    ALTER INDEX index_name REBUILD TABLESPACE USERS_IDX
    Would the command deallocated space in my USERS tablespace? So that the size of the USERS tablespace would be much smaller, since it wouldn't have the indexes.
    A quick and dirty trick to generate the rebuild commands follows

    set pagesize 0
    set head off
    spool myfile.sql
    select 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE USERS_IDX;'
    FROM USER_INDEXES
    WHERE TABLESPACE_NAME = 'USERS';
    spool off
    set echo on
    @myfile.sql
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •