Tables from One tablespace to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tables from One tablespace to another

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi guru's

    I have User_a, default tablespace is Users_data_tbs_a(data) and Users_idx_tbs_a for indexes. And all the default indexes created by Primary key and Unique key constraints are placed in Users_idx_tbs.

    And have User_b, default tablespace Users_data_tbs_b(data). and Users_idx_tbs_b for indexes. I performed following tasks to put user_a schema into user_b.

    1. created dump file using user wise export
    2. Revoked unlimited tablespace from user_b.
    3. alter user user_b identified by *****
    quota 0 on users_data_tbs_a,
    quota 0 on users_idx_tbs_a,
    quota unlimited on users_data_tbs_b,
    quota unlimited on users_idx_tbs_b;

    When try to import using

    imp user_b file=userA.dmp fromuser=usera touser=userb log=userbimp.log

    I observed, it is still trying to create default indexes of primary key and unique key constraints in the users_idx_tbs_a tablespace, and I am getting object already exists errors, so the result is tables are imported without primary/unique key constraints.

    How to resolve this problem? Please guide me, where I am doing wrong?

    My environment is SunOS, Oracle 8.1.6



    Thanks In Advance.
    Nagesh

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can not define different default tablespace for indexes. The indexes will be placed in user's default tablespace.

    You will have to rebuild the indexes to the another tablesapce.

    ALTER INDEX index_name REBUILD TABLESPACE tablespace_name

    Sanjay

    [Edited by SANJAY_G on 06-06-2002 at 01:00 AM]

  3. #3
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    The reason which I think is that index defination has user a's tablespace and while exporting it stores that defination only.Thats why when u import it tries to create in the same tablespace defined in its defination.Correct me if I am wrong.

    I could not get why you Revoked unlimited tablespace from user_b ?

    Take Care.

  4. #4
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Well Guy's


    Thanks for your prompt replies.

    Nishant :
    Basically UNLIMITED TABLESPACE System Privilege To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles. So, if you do not revoke this privilege from the user, he enjoy the space in another tablespace. In my case users_data_tbs_a, that is why I revoked this privilege from User_a. I hope I explained properly.





    Nagesh

  5. #5
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Thanks...

    Got your point.

    Take Care.

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