Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?

share|improve this question
Thanks to @ai10 for the suggestion: "For 10 & 11G, use data pump utility. Follow the tutorial here: techxploration.blogspot.com.au/2012/10/…; – Stuart Woodward Nov 5 '12 at 0:42

2 Answers

up vote 5 down vote accepted

A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is DEFAULT TABLESPACE attribute of underlying USER. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one).

In addition, each type of structure (Table/Index/Partition...) has it's own way of moving to another tablespace. (i.e. moving a table does not move indexes built upon it - which would be rather undesirable, because you want to store indexes in another filesystem for better performance). This means, that you will have to move contents of the schema object by object.

share|improve this answer
5  
Having seperate tablespaces for tables and indexes isn't really considered neccessary for performance, though it can still be done for admin reasons. See this old SO thread, or various AskTom articles. – Alex Poole Jun 20 '12 at 7:02
2  
Also, keep in mind any LOB objects (BLOB, CLOB) have their storage separate from the rest of the table, with only a pointer stored in the table structure itself. If your intent is to move an entire table and its contents, you will need to move the LOB segments independently. "alter table t move lob(y) store as ( tablespace users )" – REW Jun 23 '12 at 4:41
up vote 1 down vote

For 10 & 11G, use data pump utility. Follow the tutorial here:

http://techxploration.blogspot.com.au/2012/10/changing-tablespace-of-oracle-schema.html

It is the safest if not the best way to do it.

share|improve this answer
2  
Consider summarizing the key points in the link to beef this answer up a little since links can go stale over time. Another reason is that answers that are "barely more than a link to an external site" may be removed see the FAQ. – Paul White Nov 3 '12 at 10:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.