4 Replies Latest reply: May 22, 2009 8:07 AM by OrionNet RSS

    how to import data using datapump

    650105
      Hello I have problem to import data in schema. I have export the using datapump and create a new user to import expdp but it failed. My environment is 11g on windows server 2003. My command and error are

      Create user abc identified by abc;
      Grant dba to abc;
      grant exp_full_database, exp_full_database to abc;
      grant read, write on directory DATA_PUMP_DIR to abc;
      export terminated successfully
      now I create another user let xyz and try to import dump file which I have extracted in last process
      impdp xyz/xyz schemas=xyz directory=DATA_PUMP_DIR dumpfile=abc.dmp logfile=impdpxyz.log
      it raised error
      Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc
      tion
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      ORA-39002: invalid operation
      ORA-39165: Schema TEST10 was not found.

      Where I’m committing mistake
      Thanks in advance
        • 1. Re: how to import data using datapump
          Centinul
          Did you do any research on the error you received?
          Schema string was not found.
          Cause

          If exporting or importing over the network, either the user specified a schema name that was not found in the source database or else the user lacked the proper EXP_FULL_DATABASE or IMP_FULL_DATABASE role that would allow them to access another schema. For importing from files, the user specified a schema name not found in the dump file set.

          Action

          Retry the operation using the correct schema name.
          • 2. Re: how to import data using datapump
            OrionNet
            Hello,
            Can you post your export command as well? Also look into using REMAP_SCHEMA option, then you need to create target user upfront.
            Regards-
            • 3. Re: how to import data using datapump
              650105
              i have used remap schema option to perform this task and import successfull but one error ihave used the following command
              impdp test10/test10 REMAP_SCHEMA=ABC:TEST10 DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp LOGFILE=TEST10.log
              in log file i got this error

              ORA-31684: Object type USER:"TEST10" already exists
              one more question if i have a user with many partitons and i exported the user and import the file in another user using remap schema option. would all partitions also create in new user.
              • 4. Re: how to import data using datapump
                OrionNet
                Hello,

                Then drop TEST10 and import again, no need to create TEST10 upfront when you use REMAP_SCHEMA.

                one more question if i have a user with many partitons and i exported the user and import the file in another user using remap schema option. would all partitions also create in new user.

                Users don't have partitions but table does and yes it should import and remap everything to the new schema including partitions.

                Regards