• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4856
  • Last Modified:

how to move one schema objects to another tablespace?

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.
2 Solutions
alter table your_existing_table_name move tablespace new_tablespace_name;
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>;
dodowomanAuthor Commented:
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.

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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
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.
this question was answered.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now