moving objects from one tablespace to another

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

In 8.1.x:
alter table move tablespace new_ts;

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.

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 ????

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...

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.

you can export the table/user, import them with "indexfile=" option which will create the object scripts. Edit the file , 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)

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,

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

Quote:

---

Originally posted by 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,

---

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]

There is a very trivial way to move users objects from one TS to another with EXP/IMP.

1. Perform an EXP
2. Drop user togerher with its objects
3. Racreate the user with new default tablespace
4. THIS IS THE IMPORTANT PART: revoke user any quota on any tablespace except for the new default tablespace. Pay also attention that user haven't got UNLIMITED TABLESPACE system privilege (it used to come together with RESOURCE or CONNECT role by default)
5. IMP the dmp.

Oracle will try to create the objects in their old tablespaces as it is recorded in the dmp. But since the new user has no quota on the old tablespace the objects will be created in new default tablespace.

Bottom line: it is not sufficient to define new default tablespace for schema owner, you must also rewoke him quota from old tablespace.

HTH,

I think as long as you dont grant resource and do alter user xxx quota on yyy you should be ok, resource will grant unlimited quota on all tablespaces

I tried to change the tablespace myself. But found the following method applicable though it might not be a right approach to do.

Make a dump file of all the tables/objecs by exporting the whole user. Edit this dump file a text editor (You can use vi editor). Using find/replace command replace all the instances of tablespace names from which you want to move the objects to the name of tablespace where you want to move. When you do this keep the name of the tablespaces in Capitals and enclose them in double quotes ("). Be sure you don't make any other change to the file. Save this modified file as different filename to have a safe backup of original dump, least you might need it in case of failure.

Now drop all the objects owned by the user, better drop the user and re-create it again with proper defination of tablespace quotas, to be sure of dropping all the objects.

Now import the edited dump file as you do with any other dump file and you'll find all the tables in the required tablespace.

I guess what shrutiM is talking is use show=Y option while imprting which give you all the definitions of the objects where you can search for TABLESPACE string and edit.

For small databases/schemas it works, otherwise its time consuming and mistake prone task cuz, Iam doing it now for a test database to be resized by 1/10th of production. and I hate to do it... but, didn't find any alternative.

Otherwise, if you have enterprize manager, import with rows=n option and reorganize the schema/tablespace. It will not take that long as its only objects and there is no data. Then import data with rows=y after reorg.

[Edited by sreddy on 01-04-2001 at 09:55 AM]

I think he means editing the export dump file, if that is the case I dont think it would be a good idea for large dump files