Moving table to another tablespace online

[omegamark]

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 ...

[MJP]

Yeah. Just had a chance to do it yesterday - online. But not on 9i, on 8.1.7.2.

[omegamark]

Is it there on 8.1.6.3 version ???

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

[MJP]

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 ;

[MJP]

oops...something wrong with the format.

Here is the syntax again.

alter table tablename move tablespace newtablespacename;

[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

[NDT]

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??

[jmodic]

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]

[julian]

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!