View Poll Results: Which is the best DB for OLTP

Voters
11. You may not vote on this poll
  • Oracle 8i

    10 90.91%
  • Informix 9.3

    0 0%
  • Sql Server 2000

    1 9.09%
  • My Sql

    0 0%
Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    New Delhi
    Posts
    3

    Unanswered: Change the tablespace of a table

    HI

    I have some tables in my oracle Database which belongs to the same Owner(say MyUser). However some of the tables are in the tablespace - SYSTEM and some are in another user tablespace(say - MyTbs).

    What will be the most eficient way to change the table in tablespace - SYSTEM to MyTbs without disturbing the structure(constraints-index) and the data?

    By the way I'm using Oracle 8i(Release 3 (8.1.7) for Windows NT )

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    ALTER TABLE MyUser.YourTable MOVE TABLESPACE New_Tablespace
    /
    ALTER INDEX MyUser.YourTable_IDX1 REBUILD
    /
    ALTER INDEX MyUser.YourTable_IDX02 REBUILD
    /


    If you move the table, you must rebuild the indexes.

  3. #3
    Join Date
    Jun 2003
    Location
    New Delhi
    Posts
    3
    Originally posted by dbtoo2001
    ALTER TABLE MyUser.YourTable MOVE TABLESPACE New_Tablespace
    /
    ALTER INDEX MyUser.YourTable_IDX1 REBUILD
    /
    ALTER INDEX MyUser.YourTable_IDX02 REBUILD
    /


    If you move the table, you must rebuild the indexes.

    Thank You. But my main motive for this Query was to skip the process of rebuilding all the Indexes. You see for around 350 tables number of Indexes goes to more than 500. I have tried on 2 tables and its working fine. Is there any other way?

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by rashmi_raj_us
    But my main motive for this Query was to skip the process of rebuilding all the Indexes. Is there any other way?
    Impossible - moving the table will change its rowids, and since the index entries "point" to the table rows using the rowids ... you must rebuild them.

  5. #5
    Join Date
    Oct 2003
    Location
    Republic of Srpska, Bosnia and Herzegovina
    Posts
    35

    Move tables

    Move tables
    Connect to sqlplus as MyUser and type the commands:

    spool MoveTables.sql
    select 'alter table '|| table_name||' move table_space NEW_TBS' from user_tables
    where tablespace_name='OLD_TBS'
    spool off

    spool RebuildIndexes.sql
    SELECT 'ALTER INDEX '|| OBJECT_NAME || ' REBUILD' from USER_OBJECTS WHERE OBJECT_TYPE='INDEX'
    spool off

    Then execute scripts MoveTables.sql and RebuildIndexes.sql.


    Hope this help.

  6. #6
    Join Date
    Jan 2004
    Posts
    99
    I would be very carefull when moving tables out of the system tablespace especially if the tables are owned by system or system. If the user is fred then this should be straight forward.

Posting Permissions

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