how to move one schema objects to another tablespace?

Posted on 2005-05-09
Last Modified: 2008-01-09
dear experts,

i accidentally created a user with wrong tablespace. after he created some objects, I want to move everything he owned to another tablesapce. is ther a way to do this? thanks.
Question by:dodowoman
    LVL 13

    Expert Comment

    alter table your_existing_table_name move tablespace new_tablespace_name;
    LVL 12

    Expert Comment

    Also ensure that the user's default tablespace is changed to the new tablespace. Else, all new objects created in future might end up in the wrong TS again.

    alter user <username> default tablespace <new_table_space>;

    Author Comment

    the situation is more complicated than that.
    The old_table_space is SHARED by multiple schema. I only want to move one schema to another new_table_space. what should I do? thanks.

    LVL 12

    Accepted Solution

    1)Select all tables owned by your schema

    select * from all_tables
    where owner = upper('SCHEMANAME');

    2)Generate commands to move these tables to new tablespaces
    select 'alter table '||table_name||' move tablespace <new_ts>;'
    from user_tables;

    3)Execute the output in a sqlplus to move the tables
    LVL 4

    Assisted Solution

    You can use solution given by  catchmeifuwant plus move indexes if you need to move any with
    select 'alter index '||index_name||'  tablespace <new_ts> rebuild;'
    from user_indexes;

    It will work if objects do not have CLOB/BLOB/LONG/LONG RAW. You for this objects you might need to use export/drop/import of the objects with changing default tablespace for the schema owner.
    LVL 4

    Expert Comment

    this question was answered.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now