DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: moving objects from one tablespace to another

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #13
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Wink

    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.

  4. #14
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

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