3 Replies Latest reply on Dec 9, 2019 3:23 AM by Gaz in Oz

    Inporting dmp file for a pariticular user in oracle 11g

    s k swain

      Dear Sir,

       

      I have a dmp file called pams_backup.dmp

      log file is pams.log

       

      i want to import this dmp file to my user called sanat

       

      i have user call sanat with dba

       

      in oracle 11 command terminal more

       

      i have written like this impdp sanat/sanat@tlive file=pams_backup.dmp log=pams.log full=y

       

      it give me error ora-39151

      error ora-31684

       

      I am not able to import to this user

       

      please help

       

      thanks with gratitude

        • 1. Re: Inporting dmp file for a pariticular user in oracle 11g
          Reid Ricks-Oracle

          Please open a service request with support and provide the complete export logfile and import logfile plus any parameter files if used.  There could be various causes for those specific errors including known bugs.

          • 2. Re: Inporting dmp file for a pariticular user in oracle 11g
            EdStevens

            s k swain wrote:

             

            Dear Sir,

             

            I have a dmp file called pams_backup.dmp

            log file is pams.log

             

            i want to import this dmp file to my user called sanat

             

            i have user call sanat with dba

             

            in oracle 11 command terminal more

             

            i have written like this impdp sanat/sanat@tlive file=pams_backup.dmp log=pams.log full=y

             

            it give me error ora-39151

            error ora-31684

             

            I am not able to import to this user

             

            please help

             

            thanks with gratitude

            And what did you discover when you googled "ora-39151" and "ora-31684"

            What type of export was used to create the dmp file?  Full? Schema? something else?  It would be very helpful if you were to post the logs of both the export and the failed import.  But most importantly, you should show a little initiative by at least researching the error codes.

            • 3. Re: Inporting dmp file for a pariticular user in oracle 11g
              Gaz in Oz

              The error/s is pretty self explanetory:

              SQL> @oerr ora-39151

              39151, 00000, "Table %s exists. All dependent metadata and data will be skipped due to table_exists_action of skip"

              // *Document: NO

               

              SQL> @oerr ora-31684

              31684, 00000, "Object type %s already exists"

              // *Document: NO

               

              SQL>

              There is a table in the dump that cannot be created as it already exists, so is skipped. Which may be a good thing as it would appear you are trying to re-import pams objects into "tilive"... a live/production database? To import the object or objects into the sanat schema, you need to be using the command line option "remap_schema=from_user:to_user"

              For example:

              $ impdp help=y

              ...

              DIRECTORY

              Directory object to be used for dump, log and SQL files.

               

              DUMPFILE

              List of dump files to import from [expdat.dmp].

              For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

               

              LOGFILE

              Log file name [import.log].

              ...

              REMAP_SCHEMA

              Objects from one schema are loaded into another schema.

              ...

              $ impdp sanat/sanat@tilve DUMPFILE=pams_backup.dmp LOGFILE=pams.log DUMPFILE=ORADMPDIR REMAP_SCHEMA=pams:sanat

              Where is the Oracle directory reference "directory=datapumpdir"? and use "dumpfile=pms_backup.dmp" not "file=..." and logfile=..." not "log="

              The dumpfile will need to be accessible by the oracle database software owner, on the db server for instance and you will need to have pre-created an Oracle directory to use with datapump import. As you seem to have actually managed to run impdp it suggests you have done this already so I don't believe the command you posted is a true copy of what you have tried.