-
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
-
Yeah. Just had a chance to do it yesterday - online. But not on 9i, on 8.1.7.2.
-
Is it there on 8.1.6.3 version ???
More can you send the syntax for the same ...
Radhakrishnan.M
-
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 ;
-
oops...something wrong with the format.
Here is the syntax again.
alter table tablename move tablespace newtablespacename;
-
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
-
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??
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|