Re: Changing tablespace of an user

Martin Renner wrote in message <7i0ntq$lhj$1_at_nnrp1.deja.com>...
>Hello.
>
>I created an Oracle-user and assigned him to a wrong standard- and
>temporary-tablespace. Unfortunately, this user already created some
>tables (about 15) and filled them with some MB of data.
>
>Is there an easy way to assign this user to a different (and
>correct) tablespace without losing his data? Do I have to make an export
>of this data, change the tablespace and then import everything in the
>new table???

Here's an easy way, but depending on the size of the tables can make life hellish on your system. So try this during low peak hours.

Assume User defaults to tablespace TS1 and you want the table T1 on tablespace TS2. First, you need to make the user default to the proper ts. Issue the following queries:

    ALTER USER User DEFAULT TABLESPACE TS2;     CREATE TABLE T1temp AS SELECT * FROM T1; This should create T1temp on TS2

    DROP TABLE T1;
    CREATE TABLE T1 AS SELECT * FROM T1temp; Copy temp table (still on TS2)

    DROP TABLE T1temp;

That should do it!
Brian Peasland
peasland_at_msn.com Received on Thu May 20 1999 - 21:57:07 CDT