back to index

move all users objects to different tablespace

Oracle supports moving object to different tablespace.
Sometimes this can be very useful. I don't know how to import objects into tablespace different than original tablespace with exp/imp utilities coupled with Oracle. Usually each database is laid out differently and it's unlikely that target database layout will match source database layout.
You might have to recreate tablespaces named the same as source tablespaces, move objects around and then drop temporary tablespaces.

Let's assume that you're moving Joe's tables to George's schema located on different database. Joe's uses tablespace USERS, but George should use tablespace DEVELOPERS.

Typically you'd have done those steps:

  1. Make sure George will have rights to import into tablespace USERS. This tablespace most likely exists already on your installation, so you'll have to grant quota to George:
    alter user george quota 500M on users

  2. Import Joe's objects:
    imp 'userid=george@target' 'file=joe.dmp' 'tables=*'

Now, check which objects need to be moved:
select * from dba_segments where tablespace_name = 'USERS' and owner = 'GEORGE'

There should be different kinds of objects, usually tables, indexes and lobs.

Easiest way to move all objects from one tablespace to another is to generate script with simple SQL query: 

select 'alter table '||lower(segment_name)||' move tablespace developers;'
    from dba_segments
    where segment_type = 'TABLE' and owner = 'GEORGE'
    and tablespace_name != 'DEVELOPERS'
union all select 'alter index '||lower(segment_name)||' rebuild tablespace developers;'
    from dba_segments
    where segment_type = 'INDEX' and owner = 'GEORGE' and tablespace_name != 'DEVELOPERS'
union all select 'alter table '||lower(table_name)||' move lob('||lower(column_name)||') store as (tablespace developers);'
    from dba_tab_cols
    where owner = 'GEORGE' and data_type like '%LOB%'

This query should be adjusted to match username and target and source tablespaces.

Query doesn't take into account partitioned tables and possibly other objects, but it should be straightforward to update script so it handles those object types correctly.

Last step would be to execute script in Toad or SQL*Plus.