moving objects from one tablespace to another

[ocpdude]

Hi,
what is the easiest way to move a user with its objects
from one tablespace to another

[marist89]

In 8.1.x:
alter table move tablespace new_ts;

[sreddy]

Hard way, if you are not using the latest version oracle 8i which has trasportable tablespaces feature.

Listing out all the objects and inlude all the objects in export and import.

[ocpdude]

How about if I did and EXP FOR ALL the USERS I need
to move then drop all the users and create them again
with a new default tablespace !!!!!!! wouldn't that be
easier than doing on the table level ????

[sreddy]

The problem here is if you export full=y or specific users, it will also export the definitions of objects ( I mean definition tablespaces, the tables belongs to) and when you import, it will try to create that tablespace as part of the object definitions and imports into that tablespace if it cant find one thats already defined/precreated.

so your import fails unless otherwise it finds precreated tablespace (specifying ignore=y) or its allowed to create tablespace (ignore=n).

Its just like reorganizing your databases. The best bet is to use Enterprize Manager Reorg utility if you do have...

[gram2000]

There is no straight forward way that I know of. But this is what I have
done in the past

Assume you wanted to move table emp from tablespace user_data
to tablespace emp_data within the same schema.


rename emp to emp_old; -- so that nobody does any dml while you are moving

create table emp_new
tablespace emp_data
as select * from emp
unrecoverable --- this line will skip generating redo's
/

validate the number of rows in both table; -- Always perform this check
count(*) from emp_old;
count(*) from emp_new;

rename emp_new to emp;
create the indexes, constraints;
I don't what happens to tables that reference emp; (you have to check this)
drop and recreate any synonyms that you have to;
grant insert, update, delete to those who had before;
compile any invalid objects;

You should retain emp_old for a few weeks before you get rid of it (just in case).

This should do it I think.

[gsprince]

you can export the table/user, import them with "indexfile=<filename>" option which will create the object scripts. Edit the file <filename>, change whatever is needed. import the data without indexfile option and with rows=y,...

Your drop user option may also work. Create the user with the default tablespace and make sure to alter the user to have 0K quota on the previous default tablespace.
(use this option if u have to move most of the objects for the user)

[ocpdude]

Sreddy,
How come when IMP users it also import the tablespace found
in the dump file!!!! there is a DESTROY option DEFAULTED=N (which means don't execute CREATE TABLESPACE that exist
in the dump file).

I thought IGNORE option is used for (not)displaying errors only. (does it actually do overwrite the existing objects if it was set to Y???)

thanks,

[pando]

ignore=y will insert rows and ignore the object creation error, if the table doesnt have any constraint then you will get duplicate rows if ignore is set to N then insert rows part will be skipped if object exists

[sreddy]

[QUOTE][i]Originally posted by ocpdude [/i]
[B]Sreddy,
How come when IMP users it also import the tablespace found
in the dump file!!!! there is a DESTROY option DEFAULTED=N (which means don't execute CREATE TABLESPACE that exist
in the dump file).

I thought IGNORE option is used for (not)displaying errors only. (does it actually do overwrite the existing objects if it was set to Y???)

thanks, [/B][/QUOTE]

yes, You are right. IGNORE=y is just a flag to indicate how object creation errors should be handled.

DESTROY=N is option to reuse the existing datafile, if the datafile exists already, not to stop executing CREATE TABLESPACE command in the dump file. CREATE TABLESPACE command will be successful if its using different datafile other than one that exists, existing datafile can be used specifying DESTROY=y.

For tables, IGNORE=Y causes rows to be imported into existing tables. No message is given. If a table already exists, IGNORE=N causes an error to be reported, and
the table is skipped with no rows inserted.

Also, objects dependent on tables, such as indexes, grants, and constraints, will not be created if a table already exists and IGNORE=N.

So, you have to make sure to get objects depends on tables also be moved to the new tablespace.

[Edited by sreddy on 01-03-2001 at 01:55 PM]