Moving table to another tablespace online
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Moving table to another tablespace online

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    384
    Moving table to another tablespace online ...

    I know we can move a table from one tablespace to another tablespace .In 9i there is a new feature called moving table online ...

    Has anyone done that ...
    Radhakrishnan.M

  2. #2
    Join Date
    May 2000
    Posts
    50
    Yeah. Just had a chance to do it yesterday - online. But not on 9i, on 8.1.7.2.

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    Is it there on 8.1.6.3 version ???

    More can you send the syntax for the same ...
    Radhakrishnan.M

  4. #4
    Join Date
    May 2000
    Posts
    50
    Well from the place where I looked for this, all it says is for version 8i....so I am positive that it works on 8.1.6.3 as well.
    I looked in Reveal net's knowledge engine.

    But anyways here is the syntax:

    alter table move tablespace ;

  5. #5
    Join Date
    May 2000
    Posts
    50
    oops...something wrong with the format.

    Here is the syntax again.

    alter table tablename move tablespace newtablespacename;

  6. #6
    Join Date
    Sep 2000
    Posts
    384
    I want moving the table online ...
    Is online option there in 8.1.7.2

    SQL> alter table test move tablespace GENERAL_IDX ;

    Table altered.

    SQL> alter table test move tablespace GENERAL_DATA online ;
    alter table test move tablespace GENERAL_DATA online
    *
    ERROR at line 1:
    ORA-25148: ONLINE option not permitted


    SQL> select * from v$version ;

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
    PL/SQL Release 8.1.6.3.0 - Production
    CORE 8.1.6.0.0 Production
    TNS for Solaris: Version 8.1.6.3.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production

    Radhakrishnan.M

  7. #7
    Join Date
    Aug 2001
    Posts
    147
    Originally posted by omegamark
    I want moving the table online ...
    Is online option there in 8.1.7.2

    SQL> alter table test move tablespace GENERAL_IDX ;

    Table altered.

    SQL> alter table test move tablespace GENERAL_DATA online ;
    alter table test move tablespace GENERAL_DATA online
    *
    ERROR at line 1:
    ORA-25148: ONLINE option not permitted


    SQL> select * from v$version ;

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
    PL/SQL Release 8.1.6.3.0 - Production
    CORE 8.1.6.0.0 Production
    TNS for Solaris: Version 8.1.6.3.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production


    I don't think you need to use ONLINE option in your command. Can you try without that option??

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ALTER TABLE MOVE ONLINE is a new feature available only on 9i, not on 8i.

    In 8i, when you move the table, the table is locked in exclusive mode, so no DML operations (insert, delete, update) are permited during the table rebuilding process. Only selects on that table are allowed.

    Now on 9i if you specify ONLINE option in MOVE TABLE clause, all DML operations are allowed on that table even while it is being rebuild.

    However this kind of table rebuilds are currently only available on index organized tables (as they are realy only a kind of B*tree indexes), not on normal heap tables. In 9i releas 2 it is supposed to be available also for normal tables.
    Until then you are still stuck to oldfashioned "non-online" table moves.

    [Edited by jmodic on 02-12-2002 at 03:47 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Jurij's right. Even when moving IOTs online be careful not to neglect the overflow segment. Oracle will not move it if you don't specify so!

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