5 Replies Latest reply: Jul 12, 2013 11:38 AM by user130038 RSS

    Which objects must be created before impdp?

    user130038

      Hi there

       

      Platform: RHEL 5.8 64bit

      ENV: Source Server - Oracle 10.2.0.5 on ASM

                Target Server - Oracle 10.2.0.5 on ASM

       

      Specific schemas need to be exported from Source database and imported into target database - expdp/impdp (datapump) will be used for export and import.

       

      My questions are:

       

      1. What objects must be created (roles, synonyms, tablespaces, etc) upfront before importing the schemas?
      2. Is it possible to export all these objects (roles, synonyms, tablespaces, etc) during schema export (expdp) and have Datapump automatically create them in Target database during import?
      3. I may have to repeat the expdp/impdp steps multiples times. Do I need to drop the users/schemas and/or other objects in target database for subsequent impdp?

       

      Best regards

       

      Message was edited by: user130038

        • 1. Re: Which objects must be created before impdp?
          Fahim5

          while importing schema using impdp

          1)You just need to create tablespace  and user

          2)It gives error for tablespace and user not existing but the objects are created automatically

          3)use table_exists_action=replace/truncate while importing multiple times.

          • 2. Re: Which objects must be created before impdp?
            Richard Harrison .

            Hi,

            Answers below:

             

            1) If you are doing a full database import and all the directory paths/ASM locations can be automatically created then you dont need to pre-create anything. If however you are doing a schema at a time or a few schemas together you will need to pre-create some objects - this would include tablespace/roles/profiles etc

            2)Yes - they could be extracted from the source using full=y and include=tablespace/role etc to only extract the definitions of those objects and nothing else

            3) TABLE_EXISTS_ACTION might help here but that only allows replacement of tables. If you want to overwrite a procedure that won;t be allowed to happen. It is probably simpler to just drop the schemas each time.

             

            Cheers,

            Harry

             

            http://dbaharrison.blogspot.com

            • 3. Re: Which objects must be created before impdp?
              user130038

              Thank you for your response.

              RichardHarrison. wrote:

               

              2)Yes - they could be extracted from the source using full=y and include=tablespace/role etc to only extract the definitions of those objects and nothing else


              full=y means Full Database? I have been asked to export/import only select schemas and not full database. Would include=tablespace/role still work if I do not mention full=y?


              I know I should try this myself to verify but I do not have the required server access yet.

               

              Best regards

              • 4. Re: Which objects must be created before impdp?
                Richard Harrison .

                Hi,

                tablespaces/roles can only be extracted when full=y they are not valid for a schema extract. full=y does not mean it will extract everything though bu default it will but as soon as you say include=x the export will have access to export anything but because you said only incude tablespace thats all you will have - see example below:

                 

                this won;t work:

                 

                [oracle@server]:EIANCAPP:[~]# expdp / schemas=system include=tablespace

                Export: Release 11.2.0.2.0 - Production on Fri Jul 12 15:23:51 2013

                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

                Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                With the Partitioning option
                ORA-39001: invalid argument value
                ORA-39041: Filter  "INCLUDE" either identifies all object types or no object types.

                 

                 

                This does and only extracts the tablespace ddl:

                 

                [oracle@server]:EIANCAPP:[~]# expdp / include=tablespace full=y

                Export: Release 11.2.0.2.0 - Production on Fri Jul 12 15:24:10 2013

                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

                Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                With the Partitioning option
                Starting "OPS$ORACLE"."SYS_EXPORT_FULL_01":  /******** include=tablespace full=y
                Estimate in progress using BLOCKS method...
                Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
                Total estimation using BLOCKS method: 0 KB
                Processing object type DATABASE_EXPORT/TABLESPACE
                Master table "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
                ******************************************************************************
                Dump file set for OPS$ORACLE.SYS_EXPORT_FULL_01 is:
                  /oracle/export/EIANCAPP/expdat.dmp
                Job "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully completed at 15:24:17

                [oracle@server]:EIANCAPP:[~]#

                 

                Cheers,

                Harry

                 

                http://dbaharrison.blogspot.com

                • 5. Re: Which objects must be created before impdp?
                  user130038

                  Thank you so much Harry!