Alter table Move

[gunjan14]

Hi,
I am trying to move the table from one tablespace to another and I am getting this error.

SQL> alter table omc.temp_anuj move tablespace users;
alter table omc.temp_anuj move tablespace users
*
ERROR at line 1:
ORA-14004: missing PARTITION keyword


SQL> alter table omc.temp_anuj move parallel nologging tablespace users;
alter table omc.temp_anuj move parallel nologging tablespace users
*
ERROR at line 1:
ORA-14004: missing PARTITION keyword

The Oracle Version is Oracle 8.0.6.0.0

Why this is happening?

 

[alexhu]

It only works in 8.1 and greater

Alex

 

[gunjan14]

Is there any way we can achieve this in Oracle 8.0.6?

 

[alexhu]

yes - export table, drop table, create table in new tablespace, import table with IGNORE=Y

Alex

 

[aryeh1010]

You could also do a create table <name> as select * from <table_name> tablespace <new_tablespace_name>.

Then drop the old table and rename the newly created table.

 

[SantaMufasa]

As a warning when using Aryeh's suggestion: The only declarative constraints that migrate with &quot;CREATE TABLE...AS SELECT...&quot; are the &quot;NOT NULL&quot; constraints. All other declarative constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK) will disappear in the newly created table unless explicitly added after the CREATE.

Dave
Sandy, Utah, USA @ 19:23 GMT, 12:23 Mountain Time

 

[carp]

As well as all indexes.

 

[alexhu]

as well as all defaults set on columns......

Alex

 

[gunjan14]

Hi,

I tried that method (as suggested by aryeh) and that is workable. But there is 1 minor change.
Have to give the tablespace name before as Select statement
Its like
create table <new_table_name> tablespace <tablespace_name>
as SELECT * FROM <old_table_name>
drop table <old_table_name>
rename <new_table_name> to <old_table_name>.
But one thing Dave, if we are not able to create other constraints and its not possible to do MOVE in 8.0.6 than what option do I have to transform the constraints also with the table?


Gunjan

 

[alexhu]

At the risk of repeating myself

export table, drop table, create table in new tablespace, import table with IGNORE=Y


Alex

 

[gunjan14]

Hi
Thanks.

Gunjan