-
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?
sree
-
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;
-
Partiall
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
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
-
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?
-
Script
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
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
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
|