move table to another tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: move table to another tablespace

  1. #1
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  3. #3
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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

  4. #4
    Join Date
    Feb 2001
    Posts
    203
    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

  5. #5
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    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;

  7. #7
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    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

  8. #8
    Join Date
    Aug 2000
    Posts
    462
    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?

  9. #9
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    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
  •  


Click Here to Expand Forum to Full Width