[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Changing table's tablespace

Posted on 2005-04-28
Medium Priority
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 32

Expert Comment

ID: 13884937
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.

Expert Comment

ID: 13885032

   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;

Author Comment

ID: 13885063
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

ID: 13885070
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

geotiger earned 60 total points
ID: 13885416
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

ID: 13930632
To move your tables do:

alter table {yourtablename} move tablespace {newtablespace};

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

834 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