Re: Problems Copying User Tables To New Tablespace

You didn't mention what version what version of Oracle you are using.

Under 8.1.5 (at the latest?) and above you can move tables between tablespaces with the SQL command

alter table <table_name> move tablespace <new_tablespace_name>;

Before 8.15 (at the latest?), it's a bit more tricky.

The Import problem you are having is because (I believe) it always imports into the same tablespace that the table was exported under, regardless of what default tablespace you have specified for the new user.

Hope this helps,

Richard S.

Oracle DBA.
UK

Ken Hammer <khammer_at_umich.edu> wrote in message news:3A88380B.B58BA4E3_at_umich.edu...
> Greetings,
>
> I've looked in the FAQ (Oracle Underground) but the solutions I found
> did not work for me, so I was hoping somebody here could help me out.
>
> What I'm basically trying to do is copy a user's tables into a new
> tablespace. We have a a development and staging user set up to use the
> same table spaces. I need to get the staging users tables to a new
> tablespace. This is what I did:
>
> 1) Created new tablespace, "staging".
> 2) Created a new "new_stage" user and granted connect and resource to
> it's default table space which is "staging", (the newly created
> tablespace).
> 3) Exported the old staging users tables using the "user" option on the
> export.
> 4) Imported the tables using the "fromuser=old_stage",
> "touser=new_stage" options.
>
> However, the tables all imported back into the original tablespace, and
> there is nothing in the new tablespace I have created.
>
> What did I miss?
>
> --
> Ken Hammer
> Information Technology Division
> University Of Michigan
> khammer_at_umich.edu
Received on Wed Feb 14 2001 - 21:49:40 CET