move table to another tablespace

[dragon99]

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

[Halo]

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.

[dragon99]

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

[sree_sri]

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?

[DBAtrix]

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.

[kmesser]

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;

[padmam]

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

[kmesser]

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?

[padmam]

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