Solved

How to move an oracle user from one tablespace into another

Posted on 2007-11-20
11
4,869 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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 27

Author Comment

by:Tolomir
Comment Utility
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
Comment Utility
also Materialized Views, and triggers...



0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 200 total points
Comment Utility
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
Comment Utility
So I don't have to move them? They are always at the right place?

0
 
LVL 27

Author Comment

by:Tolomir
Comment Utility
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 34

Expert Comment

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

Assisted Solution

by:techji
techji earned 50 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now