Changing table's tablespace

Posted on 2005-04-28
Last Modified: 2008-01-09
I am using oracle 7.3.4

how can I change the tablespace of a table without having to drop and create it allover?

Any help would b greatly appreciated.
Question by:bsippy
    LVL 31

    Expert Comment

    Unfortunately, prior to 10g, you have to create a new tablespace, copy all of the objects from the old tablespace and drop the old tablespace. In 10g, you can just issue the command alter tablespace tbs1 rename to tbs2, although there are some restrictions.
    LVL 4

    Expert Comment


       You can move a table from one tablespace to another tablespace

       alter table tblname move Tablespace tablespacename;


      My tablename is EMP and my tablespace name is User in which EMP is residing....
      I want to move emp in to another tablespace called DATA then i will issue the following command

      alter table EMP move tablespace DATA;
    LVL 2

    Author Comment

    I have already tried using

    'alter table EMP move tablespace DATA'

    but it is giving me the error ORA-01735: invalid ALTER TABLE option.

    Is there any other way out
    LVL 4

    Expert Comment

    I am very sorry for my last post....

     I didnt noticed the 7.3.4

     You can do a table level export and do a import

    LVL 12

    Accepted Solution

    As Awkinq00 has pointed out, you have no other choice but to re-create it and drop it in version 7.3.4. Here are the steps

    1. create a new user to use different tablespace as default:

    create user new_user identified by secret
    default tablespace new_ts
    temporary tablespace user_temp
    quota unlimited on new_ts;

    2. In old user grant access:

    grant select on old_table to new_user;

    3. Log into new user:

    create table new_table as select * from old_user.old_table;

    You could also use exp/imp

    exp old_usr/pwd@db tables=old_table file=myfile.dmp

    imp new_usr/pwd@db file=myfile.dmp fromuser=old_usr touser=newuser

    You may need to use a DBA account to do the exp/imp.


    LVL 25

    Expert Comment

    To move your tables do:

    alter table {yourtablename} move tablespace {newtablespace};

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now