How to move a table from a tablespace to another?

I initially created tables from a user account with tablespace A
I now have changed the default tablespace of user to B
is there any way to move the extends and data from tablespace A to B ?

Ans1:

you can move an index with the command REBUILD but as far as I know you can
not move the table.

Ans2:
About all you can do is something like create a table with a slightly
different name from the original. You could simply do CREATE TABLE
newtable AS SELECT * FROM oldtable. Since you've changed the default
tablespace for the user, the new table will be created in the new
tablespace. Once done, just drop the original table and rename the new
table to the original table name.

Ans3:
Hi Naveen -

Yes you can, but involves little work. I had done this while ago so make
sure that parameters are correct as you go through these steps.

First export the specific table(s) to a dump file such as xyz.dmp under
dba account such as ops$dba.

exp parfile=export_tables_parfile.txt

The content of this parfile, export_tables_parfile.txt is given below.
To import all the tables, delete tables=(...,...)

userid = /
file    =/r1/export/xyz.dmp
rows    =n
log     =$HOME/log/export.log
tables =(
   table1,
   table2
)
____________________________________________________________________________

Then import the tables. This import will ceate only the script file,
import_script.sql. THE STRUCTURES AND DATA WILL NOT BE IMPORTED.

imp parfile=import_tables_parfile1.txt

Contents of the import_tables_parfile1.txt is given below. The file,
import_script.sql will contain the SQL script for tables (table1, table2 in
this case), their indexes, constraints, grants, triggers etc. with
appropriate storage parameters. Alter this script to suit your needs and
use it to create the tables/indexes/grants/constraints/triggers or any of
the above in the appropriate tablespace for any user with your own storage
parameters.

userid = /
file    =/r1/export/xyz.dmp
log     =$HOME/log/import_script.sql
show = y
tables =(
   table1,
   table2
            )
----------------------------------------------------------------------------

----------------------------------------------
Drop the tables. Before dropping production tables, you may want to make
sure that your export file,xyz.dmp is good by importing few tables in
some account, ops$abc, as given below or by copying them to something else.

----------------------------------------------------------------------------

-----------------------------------------------
Use the above altered script to create the tables in appropriate tablespace
and/or user.
----------------------------------------------------------------------------

-----------------------------------------------
Then do the real import with the following script. You may want to add
other import options to this script.
Find out about these option by entering: imp help = y.

imp parfile=import_tables_parfile2.txt

Contents of the import_tables_parfile2.txt is given below.

userid = /
commit =y
file    =/r1/export/xyz.dmp
fromuser=ops$dba
touser = ops$abc
ignore =y
log     =$HOME/log/importing_in_new_tablespace.log
tables =(
   table1,
   table2
            )

Good luck !!!

Oracleguru
www.oracleguru.net
[email protected]

Ans4:
The exp/imp solution which has been suggested to you is the good one but
I think it requires a few additional precisions. I don't think that
naming the log file something.sql, although clever, is a really good
idea, unless your favorite pastime is spending hours with your text
editor reformating SQL statements (when imp displays SQL statements,
they are cut in fixed size chunks, regardless of 'natural' boundaries,
each one enclosed between double quotes).
Basically when exp exports a table, it saves the CREATE statement which
includes everything including the tablespace name. When imp executes
this CREATE :
- either the table already exists and the the data is not loaded unless
you specify IGNORE=Y
- or the table does not exist and the table owner is allowed to create a
table in the specified tablespace, and then the table is recreated
(although in one single piece by default) more or less as it was,
- or the table does not exist and the table owner CANNOT create tables
in the specified tablespace, in which case it is created in the default
tablespace for the user (if, once again, the user can create tables in
his/her default tablespace).

This is why what you must do is :

exp username/password owner=username file=username.dmp

then, from a DBA account :

alter user username
default tablespace B
quota unlimited on B
quota 0K on A;

and then

imp username/password file=username.dmp full=Y commit=Y

Note that if you had indexes in another tablespace than A and you also
want to move them, you will have either to rebuild the indexes as
somebody suggested you, or use the INDEXFILE parameter of imp to
generate a (directly usable!) .sql which contains the CREATE INDEX
statements which you can edit and modify. In this case, you can import
without the indexes and run the .sql afterwards. This being Oracle, you
will still have problems with the indexes associated with PRIMARY KEY
and UNIQUE constraints, but, well ...
--
Regards,

Stéphane Faroult
Oriole Corporation

Hosted by www.Geocities.ws