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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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.
For 10 & 11G, use data pump utility. Follow the tutorial here:
It is the safest if not the best way to do it.