Changing user's tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Changing user's tablespace

  1. #1
    Join Date
    Feb 2001
    Posts
    100

    Question

    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




  2. #2
    Join Date
    Oct 2002
    Posts
    23

    export

    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
    Michellea
    "Live Life to the Fullest"

  3. #3
    Join Date
    Feb 2001
    Posts
    100
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    ALTER TABLE owner.myTab MOVE TABLESPACE new_ts;
    ALTER INDEX owner.myIdx REBUILD TABLESPACE new_ts;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Posts
    172
    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.


  6. #6
    Join Date
    Feb 2001
    Posts
    100
    Thanks All,

    I will try Alter ..Move

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    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.



  8. #8
    Join Date
    May 2002
    Posts
    2,645

    Re: export

    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.

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Re: Re: export

    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]
    -nagarjuna

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    probably meant use an index file and import from that (one would hope anyway)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width