Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to move an oracle user from one tablespace into another

Posted on 2007-11-20
11
Medium Priority
?
4,933 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 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