Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to move an oracle user from one tablespace into another

Posted on 2007-11-20
11
Medium Priority
?
4,957 Views
Last Modified: 2013-12-19
Hello,

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,
Tolomir
0
Comment
Question by:Tolomir
11 Comments
 
LVL 27

Author Comment

by:Tolomir
ID: 20319323
These users all got tables views, functions, procedures ... whatever, so a simple "delete user, recreate user" is NOT an option...

0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20320330
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;
@somefile.sql
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20320354
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.
begin
  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;
end;

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 27

Author Comment

by:Tolomir
ID: 20320409
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.

0
 
LVL 27

Author Comment

by:Tolomir
ID: 20320441
also Materialized Views, and triggers...



0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 800 total points
ID: 20320617
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.
0
 
LVL 27

Author Comment

by:Tolomir
ID: 20320678
So I don't have to move them? They are always at the right place?

0
 
LVL 27

Author Comment

by:Tolomir
ID: 20320688
so basically I move tables & indexes of a user and are done the a user schema?

Good give it a try tomorrow, thank you.

Tolomir
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20321630
Correct, you do not have to move them.
0
 
LVL 9

Assisted Solution

by:techji
techji earned 200 total points
ID: 20325943
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.
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 1000 total points
ID: 20326750
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 :-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

877 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