Re: Move user to different tablespace

You didn't mention version of Oracle...
On 8i and later you can do the following:

alter user xxx default tablespace new_tbs then connect as xxx and

begin
 for t in (select table_name from user_tables) loop

• won't work for tables with LONGs begin execute immediate 'alter table '||t.table_name||' move tablespace new_tbs'; exception
  • ignore errors when others then null; end; end loop; for i in (select index_name from user_indexes where index_type in ('NORMAL','BITMAP')) loop
    • do not process LOB/domain indexes begin execute immediate 'alter index '||i.index_name||' rebuild tablespace new_tbs nologging'; exception
  • ignore errors when others then null; end; end loop; end; /

be sure quota on new_tbs for user is adequate to fit the objects. The PL/SQL above is not omnipotent, but it should move most of the tables and indexes in an average schema (if not all). For tables with LONGs export table data/drop/create with new storage/import table data is the fastest way to alter table storage, though you may also consider create new table with the same layout as old, but new storage, copy data from old into new in a pl/sql loop, drop old, and rename new to old.

hth. Corrections and additions welcome.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Linda Lee" <goforticket_at_yahoo.com> wrote in message
news:f901fb80.0205141257.3b72a895_at_posting.google.com...


> Hi,

>

> I created a user in a wrong (default) tablespace, and then all the

> user's object were created in the tablespace.

>

> How can I move the user's schema from one tablespace to another

> tablespace? I tried "export/drop user/create user/import". But it

> seems to me that the user's priviledges and grants are all lost after

> doing that.

>

> Is it a better way to do that?

>

> thanks.