Solved

How to move an oracle user from one tablespace into another

Posted on 2007-11-20
11
4,889 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 200 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 50 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 250 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date show only hh:mm 2 50
Oracle Nested table uses ? 2 60
MS SQL Server Management Studio R2 4 61
replicate in oracle 13 46
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

710 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