move table to another tablespace

Hi, all
I read recently that I can move table from one tablespace to another. I'd like to know
1. what is the syntax for that?
2.Do I have to offline the tablespace to do it?
Thanks for help.

Dragon

1. This is a feature of 8i. I think the syntax is: alter table table_name move tablespace ts_name;
2. AFAIK, no
-
Confirm this.

Thanks, Halo,
I did move the tables from one tablespace to another. But I have one table can not be moved, the error is :

ORA-00997: illegal use of LONG datatype

What can I do to move the table with datatype LONG? Is there another way to do it instead of dropping and recreating it? Thanks again.

Dragon

What is the command you used to move tables from one tablespace to another tablespace.

alter table username.temp move tablespace ;

Do you need any privs need to this move?
Can we move any user tables?

To move tables with a LONG column you can

1. export/import
2. use SQL*Plus COPY command. note that COPY is a SQL*Plus command, not a SQL command.

d.

I don't know the performance implications of the two methods described below, but you could also use CTAS:

create table newtable tablespace destinationtablespace as select * from oldtable;

Hi Kmesser, 28th April 2001 19:42 hrs chennai

When you do like that all the constraints wont be maintained for the new table.

Cheers

Padmam

padman,

Good point. Thanks!

Do you have a script to identify all the constraints and maybe disable andspool them to a .sql file for later recreation/enabling?

Hi Kmesser, 29th April 2001 19:45 hrs chennai

Here is a sample example attached below for chk cons ||rly you can construct one or some one can even give better than this in one capsule(sql script).

I got this from a website.

set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/* */
/* script to spool a listing of all constraint */
/* statements required to rebuild table. */
/* Contents: Table name, col definitions and */
/* storage parameters. */
/* */
/* @c:\oracle\sql\bld_con2.sql */
/* */
/* >> For a specific table name << */
/* >> Only check contraints << */
/* */
/* ************************************************ */

/* set termout off; */
set echo off;
set feedback off;
set long 50;

col dummy noprint format a1;
col dummy2 noprint format a1;
col table_name noprint format a1;
col con noprint format a1;
/* col command format a70; */

/* ********************************* */
/* 1. Generate alter table commands */
/* ********************************* */

/* select 'alter table '||A.owner||'.'||A.table_name||' ADD CONSTRAINT '||A.CONSTRAINT_NAME||' CHECK ('
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%' and /* Exclude NOT NULL */
A.SEARCH_CONDITION is NOT NULL
UNION
select to_char(A.SEARCH_CONDITION)
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%' and /* Exclude NOT NULL */
A.SEARCH_CONDITION is NOT NULL; */

select substr(to_char(A.SEARCH_CONDITION),1,100)
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%';

/* undefine TABLE_NAME; */
set heading on;
set pagesize 100;
set termout on;

commit;


Cheers

Padmam