1 2 Previous Next 16 Replies Latest reply on Mar 23, 2009 8:57 PM by 688835

    remap tablespace using imp

      hi ,

      how to remap the tablespace using imp command. but not impdp. can any one help me?


        • 1. Re: remap tablespace using imp

          There is no such thing REMAP in oracle 9i import utility but there is transportable tablespace option.

          see followign document link for options and examples.


          Question: What exactly is the intent or purpose here?

          • 2. Re: remap tablespace using imp
            what do u mean by remap?
            1 person found this helpful
            • 3. Re: remap tablespace using imp
              we have serveral database version like 8i,9i,10g,11i.we want to try in oracle 9i database to use the remap tablespace.how ?.please let me know

              your assistance highly appreciated.
              • 4. Re: remap tablespace using imp

                You cannnot ues remap in 8i , 9i. 10g and above you can use datapump utility to remap tablespaces. Question is what exactly you want to do ? Post one simple test case scenario involving 2 different versions).

                • 5. Re: remap tablespace using imp
                  moves any objects from one tablespace or datafile or schema into targer db user.
                  • 6. Re: remap tablespace using imp
                    in 10g database we are able to use remap tablespace.we have a two schema in 10g .say for example schema name is :A(having records with same tablespace name)and B(no records with same tablespace name). i have taken export from A schema and import into B schema) . it was succesfully imported.
                    this command i had used during import
                    (impdp username/pwd DIRECTORY=directoyname DUMPFILE=VAN.dmp logfile=a.log
                    afterthat i had gone through saw the dba_segments .it has been growing the B tablespace.

                    while importing time A tablespace Does not exist using imp command.

                    but when we are using exp and imp (take example schema name is A and B) it was growing tablespace A only in segments but B tablespace is not grow.please let me know if u have any idea.

                    please help me .
                    • 7. Re: remap tablespace using imp

                      Scenario 1 : Moving object with in the same schema to different tablespace; This wil work on all the version.


                      Scenairo 2: Moving or copying objects with in same database to different schema. This will also work on 2 different database as well.

                      1. use of dblink and create table on target from source table or insert.

                      2. export from source schema and import. Assign a default tablespace to target schema user and import.

                      Use of datapump can be considerred 10g and above between different databases on different db servers

                      • 8. Re: remap tablespace using imp
                        Dean Gagne-Oracle

                        I seem to remember that imp may do this for you automatically. Let's say in the source database you have user_1 going to old_tablespace_1 (this is what is in the dumpfile). Now on the target database you want user_1 to now go to new_tablespace_1. I seem to remember that all you have to do is precreate user1 and have the default tablespace be new_tablespace_1 and make sure that old_tablespace_1 does not exist on the target system. When the create fails, I thought it retired it using the default user tablespace.

                        1 person found this helpful
                        • 9. Re: remap tablespace using imp
                          schema name is A having a 300 table.i want move the object to different tablespace or schema in oneshot while using imp.
                          • 10. Re: remap tablespace using imp
                            (EXP&IMP)if i create old_tablespace in new db it will work fine. but my question is do i create old tablespace in new db. please let me know.

                            but does not require for datapump .
                            • 11. Re: remap tablespace using imp

                              h4. You dont' have to create same old tablespace in new schema just one default tablespace

                              You need to do this, are moving with in same schema or different schema. In any case this how you do it
                              exp system/manager owner=userA file=myexport.dmp log=myexport.log
                              h4. Before import make sure target userA have only one default tablespace only one.

                              Also create any ROLE if there is any exist for userA before import Then drop all the old objects from target userA.
                              imp system/manager fromUser=userA toUser=userA file=myexport.dmp log=myexport.log
                              Hope this helps and answers your questions.

                              Edited by: OrionNet on Mar 9, 2009 5:41 PM
                              • 12. Re: remap tablespace using imp
                                thanks for giving dump command details.
                                and one more thing we had tried your command which u have send 5 min back.it was ok.

                                imp system/manager fromUser=userA toUser=userB file=myexport.dmp log=myexport.log

                                but my question is userA having AB tablespace and userB having BC tablespace.

                                when i look at dba_segments table in system user

                                AB tablespace only growing the extends.but it is not happen in the BC tablespace.

                                why ?

                                please help me
                                • 13. Re: remap tablespace using imp

                                  Like I mentioned earlier make sure userB just have one default tablespace (BC) and quota only on BC. So if you have unlimited quota for userB on all tablespaces, then objects will go to original tablespace and not the tablespace you want to put in(to default tablespace).

                                  Revoke any unlimited tablespace privs from userB and just have one default tablespace.
                                  REVOKE  UNLIMITED TABLESPACE FROM USERB;
                                  Hope this helps, if it does mark any post helpful or answered.

                                  • 14. backup restore
                                    hi ,
                                    i have taken full export from source db . do we need to create those schema as there are in export dump(source db) before importing into target db?.

                                    could u help me
                                    1 2 Previous Next