5 Replies Latest reply: Jan 10, 2013 10:15 AM by Dean Gagne-Oracle RSS

    Importing .DMP - Is needed to know the tablespaces this one was using?

    andredecotia
      Hi all, I'd like to know if is a requisite to know the list of tablespaces the database was using to import the dumpfile on
      a different environment...
        • 1. Re: Importing .DMP - Is needed to know the tablespaces this one was using?
          861120
          Yes is need to import the data in the same place (tablespace name) where the data was backup.

          In Data Dump (expdp), you can use the REMAP_TABLESPACE for change your tablespace to one new.

          For example:
          impdp / REMAP_TABLESPACE=OLD_TS:NEW_TS DIRECTORY=expdir DUMPFILE=data.dmp LOGFILE=data.log
          Regards
          • 2. Re: Importing .DMP - Is needed to know the tablespaces this one was using?
            Fran
            depend of the exp or expdp you did. If you did a full export the tablespaces and database accounts will be automatically created.
            • 3. Re: Importing .DMP - Is needed to know the tablespaces this one was using?
              andredecotia
              thanks a lot everybody for clearing it up...

              could you plz. tell me the difference between exp and expdp? I do know when use exp must use on other side expdp and same works for imp and impdp, isn't it?
              • 4. Re: Importing .DMP - Is needed to know the tablespaces this one was using?
                Fran
                Data Pump (impdp or expdp) is the improved version of the export/import.

                If you use exp you must use imp or if you use datapump to export must use datapump to import, cant use data pump to import a dumpfile exported with exp.

                For more info:
                http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm#i1008963

                Please, when the question will be resolved close this thread marking the post as answered. Keep the forum clean!
                • 5. Re: Importing .DMP - Is needed to know the tablespaces this one was using?
                  Dean Gagne-Oracle
                  Hi,

                  exp is an old unsupported tool that was replace by expdp, also known as Data Pump. In both cases you kind of need to know the tablespace information. The only time this is not true is if your directory structure on the source and target are the same and if you are doing an full=y export job. In this case, the tablespaces will be created for you in the same location. If your directory structure changed, then the tablespaces can't be created because the datafiles can't be created. The create tablespace will be something like:

                  create tablespace foo data file '<complete directory spec here with fil name at the end> size ...

                  For example, if you changed from VMS where your directory is something like $DB100:[000000.abc.def]

                  and if your source was Linux, this would never be able to be created.

                  If you don't have full, or if your directory structure is different, then what most people do is create the tablespaces before hand or remap the tablespace to an existing tablespace. To get the name of the tablespaces needed, you could do something like"

                  impdp user/password <your normal parameters here> sqlfile=my_sqlfile.sql

                  This will not import anything, but it will put all of the commands (ddl) in this file. You can then edit the file and look for TABLEPSACE to see what tablespaces are needed. You could then either pre-create them, or you could issue some remap_tablespace parmeters.

                  Hope this helps.

                  Dean