11 Replies Latest reply: Feb 15, 2013 9:25 AM by 989272 RSS

    exdp and impdp help

    989272
      Hi Folks

      I have a database orcl1 which is being running for last one year. Just now installed a new database oracl. Both are 11G databases with same versions. I did a schema(abc) expdp from orcl1. On the fresh database oracl did a schema impdp. Schema id abc doesn`t exist in oracl as the database is fresh. When I did the impdp, the impdp says user doesn`t exist, tablespace doesn`t exist etc.

      By using impdp the database should be able to create all the existing schemas or tablespaces by default. Is there something wrong?. I need the same resources that exist in orcl to oracl. Do I need to create the user and tablespace and then proceed to impdp?

      -Regards
      Rah
        • 1. Re: exdp and impdp help
          sb92075
          986269 wrote:
          Hi Folks

          I have a database orcl1 which is being running for last one year. Just now installed a new database oracl. Both are 11G databases with same versions. I did a schema(abc) expdp from orcl1. On the fresh database oracl did a schema impdp. Schema id abc doesn`t exist in oracl as the database is fresh. When I did the impdp, the impdp says user doesn`t exist, tablespace doesn`t exist etc.

          By using impdp the database should be able to create all the existing schemas or tablespaces by default. Is there something wrong?. I need the same resources that exist in orcl to oracl. Do I need to create the user and tablespace and then proceed to impdp?
          yes, required objects must exist prior to impdp.
          • 2. Re: exdp and impdp help
            ji li
            You do not need to pre-create the user, however, the same tablespace needs to exist if you want it to use the same tablespace for the user, otherwise, it will use the default tablespace that is set for all users of the database (SYSTEM unless you have changed it).
            • 3. Re: exdp and impdp help
              TSharma-Oracle
              If you are importing with sys,system or any other priviliged user, it should create a schema for you. You can also import data to existing different tablespace by using "remap_tablespace=A:B" parameter in impdp.

              Please also most your impdp script for more clarity.
              • 4. Re: exdp and impdp help
                Srini Chavali-Oracle
                Pl post the complete expdp and impdp commands used, along with the first 20 lines of the expdp and impdp log files.

                HTH
                Srini
                • 5. Re: exdp and impdp help
                  989272
                  That means whenever I import a schema, first I need to create same tablespace that existed in source database. Is that right?
                  • 6. Re: exdp and impdp help
                    TSharma-Oracle
                    Did you see my post? you can use REMAP_TABLESPACE parameter to import your data into a different tablespace. Check documentation.

                    REMAP_TABLESPACE

                    Default: There is no default

                    Purpose
                    Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.

                    Syntax and Description

                    REMAP_TABLESPACE=source_tablespace:target_tablespace

                    Multiple REMAP_TABLESPACE parameters can be specified, but no two can have the same source tablespace. The target schema must have sufficient quota in the target tablespace.

                    Note that use of the REMAP_TABLESPACE parameter is the only way to remap a tablespace in Data Pump Import. This is a simpler and cleaner method than the one provided in the original Import utility. That method was subject to many restrictions (including the number of tablespace subclauses) which sometimes resulted in the failure of some DDL commands.

                    By contrast, the Data Pump Import method of using the REMAP_TABLESPACE parameter works for all objects, including the user, and it works regardless of how many tablespace subclauses are in the DDL statement.

                    Restrictions
                    •Data Pump Import can only remap tablespaces for transportable imports in databases where the compatibility level is 10.1 or later.

                    •Only objects created by the Import will be remapped. In particular, the tablespaces for preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to SKIP, TRUNCATE or APPEND.

                    Example
                    The following is an example of using the REMAP_TABLESPACE parameter.
                    impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1
                    DUMPFILE=employees.dmp

                    Read this link for more information:
                    http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm
                    • 7. Re: exdp and impdp help
                      989272
                      @-Sharma

                      remap_tablespace is helpful. For example, if I specify remap_tablespace=TS1:TS1, where TS1 does not exist in target db, will the target database create TS1 after I issue the impdp.
                      • 8. Re: exdp and impdp help
                        TSharma-Oracle
                        No It won't create but you can provide any existing tablespace. It will load all your objects in that existing tablespace.
                        • 9. Re: exdp and impdp help
                          Step_Into_Oracle_DBA
                          986269 wrote:
                          @-Sharma

                          remap_tablespace is helpful. For example, if I specify remap_tablespace=TS1:TS1, where TS1 does not exist in target db, will the target database create TS1 after I issue the impdp.
                          YES, for remap_tablespace option, you have to mention a tablespace which is already existing in the database.

                          Say for Eg,

                          In your case a schema backup which has objects in TS1 tablespace, so using remap_tablespace option you can map TS1 to already existing tablespace. For eg you can remap to USERS tablespace which is already there in database.

                          remap_tablespace will not create any new tablespace
                          • 10. Re: exdp and impdp help
                            Richard Harrison .
                            Hi,
                            A 'full' database expdp will contain the create tablespace commands to recreate everything - however if both database are on the same server this won;t work anyway as the paths will be the same.

                            You should manually create the tablespace names/sizes to match your original database in your new database.

                            impdp will then create the user itself - it's just the tablespace creation causing you a problem.

                            Currently the user is likely failing to create as the tablespace it has as its default does not exist. As the user fails then all the other objects fail too.

                            Cheers,
                            Harry
                            • 11. Re: exdp and impdp help
                              989272
                              Yesterday I used remap_tablespace option, the import was successful.

                              Thanks All