How to move an oracle user from one tablespace into another


we want to migrate a database from one server to another. It's a dev database.

To consolidate the structure we want to move several users with their tablespaces and datafiles all back into the tablespace users.
So a solution could either be to make these changes in the old (new) database or during the import with the oracle datapump.
Precisely I need help how to accomplish that task.

Thank you,
LVL 27
Who is Participating?

Improve company productivity with a Business Account.Sign Up

AkenathonConnect With a Mentor Commented:
Tolomir, just some clarification on the concepts:
- Not every object occupies space in a tablespace. Only SEGMENTS do. A segment is created for every non-partitioned table, table partition, non-partitioned index, index partition, cluster, materialized view, temporary segment, undo segment, and some other miscellaneous things. All of them can be thought of as "the things that occupy space", and are seen at DBA_SEGMENTS (or sys.seg$). If it's not there, it does not have a segment so you cannot/should not move it
- Other things like stored code and synonyms are stored in some other SYS tables (thus in the SYSTEM tablespace). You can look at them in DBA_OBJECTS (or sys.obj$), plus some specific to the object (e.g. DBA_SOURCE, DBA_SYNONYMS)
- Schemas don't "reside" in any particular tablespace. On the contrary, it's EACH SEGMENT that is created on a particular tablespace. So your users may well have some table or index segments on USERS and some other elsewhere
- Moreover, each segment may well have one extent (i.e. bunch of contiguous datafile blocks that is allocated as a unit for a specific segment) in one datafile and the other in another datafile... of course belonging to the same tablespace both of them!

So, where does this leave you? Back to the first answers you got: you move tables around using ALTER TABLE ... MOVE TABLESPACE USERS, and indexes using ALTER INDEX ... REBUILD TABLESPACE USERS, depending on whether you want to move a segment associated with a table or an index.

BUT!! Be warned that your indexes will get UNUSABLE after you move the table segment they index, so if I were you, I'd ensure that you move a table and IMMEDIATELY rebuild its indexes before moving the next table.

I'd like to add something that will definitely come handy for your task and maybe avoid your getting punched by your users: You can optionally append the ONLINE keyword to the index rebuild commands to enable your crew to use them while the rebuild is being done :-)
TolomirAdministratorAuthor Commented:
These users all got tables views, functions, procedures ... whatever, so a simple "delete user, recreate user" is NOT an option...

You can do this before or after the export/import (i.e. do it on PROD or DEV):

spool somefile.sql;

SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE users;'
FROM dba_tables
WHERE owner = 'SCOTT';

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE users;'
FROM dba_indexes
WHERE owner = 'SCOTT';

spool off;
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

johnsoneSenior Oracle DBACommented:
This should move any tables for <own> that are not in the USERS tablespace to that tablespace, then rebuild any indexes on that table into the USERS tablespace.  The final step is to move any remaining indexes into the USERS tablespaces that are not already in that tablespace.  The last index step is necessary to move any indexes for tables that were already in the USERS tablespace, but the index was not.
  for c1rec in (select owner, table_name from dba_tables where owner = '<own>' and tablespace_name != 'USERS') loop
    execute immediate 'alter table "' || c1rec.owner || '"."' || table_name || '" move tablespace users';
    for c2rec in (select owner, index_name from dba_indexes where table_name = c1rec.table_name and table_owner = c1rec.owner) loop
      execute immediate 'alter index "' || c2rec.owner || '"."' || c2rec.index_name || '" rebuild tablespace users";
    end loop;
  end loop;
  for c3rec in (select owner, index_name from dba_indexes where owner = '<own>' and tablespace_name != 'USERS') loop
    execute immediate 'alter index "' || c3rec.owner || '"."' || c3rec.index_name || '" rebuild tablespace users';
  end loop;

Open in new window

TolomirAdministratorAuthor Commented:
Great suggestions thank you both.

Problem is as I've mentioned there is more than tables and indexes.

I've got procedures, views,synonyms and functions too.

TolomirAdministratorAuthor Commented:
also Materialized Views, and triggers...

johnsoneConnect With a Mentor Senior Oracle DBACommented:
Procedures, triggers, views, synonyms, function, materialized views and triggers are all part of the data dictionary and are stored in the SYSTEM tablespace.  You cannot move them.
TolomirAdministratorAuthor Commented:
So I don't have to move them? They are always at the right place?

TolomirAdministratorAuthor Commented:
so basically I move tables & indexes of a user and are done the a user schema?

Good give it a try tomorrow, thank you.

johnsoneSenior Oracle DBACommented:
Correct, you do not have to move them.
techjiConnect With a Mentor Commented:
I would have to say datapump is the quickest solution. Depending on the size of the database, datapump can finish the job in few minutes vs many hours trying to move the db objects. Also for online reorgs, you would need at least double the db storage, cause you will have copies of the same db objects during the reoorg.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.