Changing user's tablespace

[Hakimca]

Hi all,

I created a user U with default tablespace X. Now I create a tablespace Y and want to move all the objects of the user U from the the tablespace X to the tablespace Y.
What's the easy way to do it without droping and recreting objects

Thanks in advance for your help

[msouthern]

1)first export the users data
exp user/password@servicename owner= consistent=y file=

2)delete the user

3) create the new tablespace

4) create the user in the new tablespace.

5) vi the filename.dmp that was created and change the tablespace to the new tablespace.

6) imp the filename.dmp into the user.

Michellea

[Hakimca]

Thanks Michellea,

Oracle is on NT. I don't have vi. I tried to do that with Wordpad editor but the dump file was so big. So I failed to do it.

[marist89]

ALTER TABLE owner.myTab MOVE TABLESPACE new_ts;
ALTER INDEX owner.myIdx REBUILD TABLESPACE new_ts;

[kburrows]

I believe with 8i you can use the

alter TABLE DEPT move tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

alter INDEX PK_DEPT rebuild tablespace users
storage ( initial 10240 next 10240
minextents 1 maxextents 121
pctincrease 50 freelists 1);

Just make sure you then change the users default tablespace to the one you want it to be.

[Hakimca]

Thanks All,

I will try Alter ..Move

[mike9]

With 8i you should use localy mananged tablespace and event if not you should alwasy set pctincrease 0.To limit the number of extents to 121 doesent make sense to me.

[stecal]

Originally posted by msouthern


5) vi the filename.dmp that was created and change the tablespace to the new tablespace.

Michellea

Edit the binary dump file? I learn someting new everyday.

[nagarjuna]

Originally posted by stecal

Originally posted by msouthern


5) vi the filename.dmp that was created and change the tablespace to the new tablespace.

Michellea

Edit the binary dump file? I learn someting new everyday.

Edit the binary file and make it unusable

[Edited by nagarjuna on 10-12-2002 at 07:24 AM]

[davey23uk]

probably meant use an index file and import from that (one would hope anyway)