9 Replies Latest reply: Sep 4, 2013 10:23 AM by Kamesh RSS

    understanding a impdp(remap_tablespace)

    Kamesh

      Dear Experts,

       

      I am new to Oracle database and trying to understand the IMPDP (remap_tablespace) functionality.Please clear my understanding (it might sound silly,but i m trying to understand the concept here)

       

      By using remap_tablespace in impdp we can move the contents(table,index etc) available in source tablespace to destination tablespace accross different database.

       

      In my testing environment,i have succesfully exported the dump of a tablespace(SOUTHERN_TB which has a schema called southern and has 3 tables)

       

      Now i am trying to import the dump into the new tablespace KAMESH available in same database.so that i can see all the 3 tables imported to KAMESH tablespace.(please correct me if my understanding is wrong!)

       

      When i try to import i am getting the below error.can you please try to make me understand the concept in a better way.

       

       

      C:\Users\Pranav>impdp directory=Datapump_testing dumpfile=SOUTHERN_TB.DMP schema

      s=southern remap_schema=southern:kamesh REMAP_TABLESPACE=SOU

      THERN_TB:KAMESH

       

       

      Import: Release 11.2.0.1.0 - Production on Tue Sep 3 19:27:17 2013

       

       

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

       

       

      Username: sys as sysdba

      Password:

       

       

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

      Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      ORA-31655: no data or metadata objects selected for job

      ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."EXPORT_ORA" WHE

      RE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas

      .

      Master table "SYS"."EXPORT_ORA" successfully loaded/unloaded

      Starting "SYS"."EXPORT_ORA":  sys/******** AS SYSDBA directory=Datapump_testing

      dumpfile=SOUTHERN_TB.DMP schemas=southern job_name=export_ora remap_schema=south

      ern:kamesh REMAP_TABLESPACE=SOUTHERN_TB:KAMESH

      Job "SYS"."EXPORT_ORA" successfully completed at 19:27:25

       

       

      question 2:

       

      when there is already 3 tables available in source tablespace(SOUTHERN_TB),is it still possible to have the same tables in KAMESH tablespace through impdp function?

       

      Thanks,

      Kamesh

        • 1. Re: understanding a impdp(remap_tablespace)
          sb92075

          >when there is already 3 tables available in source tablespace(SOUTHERN_TB),is it still possible to have the same tables in KAMESH tablespace through impdp function?

          NO!

          Objects are unique & can only exist once.

          In  you case the import will fail since the objects already exist.

           

          The SYS schema/user should NEVER be used for daily use.

          SYS schema should only be used for database patches & version upgrades.

           

          establish your own schema to use & experiment with.

          • 2. Re: understanding a impdp(remap_tablespace)
            Kamesh

            Thanks sb92075.

             

            Few more questions,

             

            1) So the remap_tablespace is used to move the objects from one  tablespace to other tablespace in different database?

             

            2) If the source tablespace(SOUTHERN_TB) is deleted then there wont be any objects.In this case can i use remap_tablespace to move the objects to newly created tablespace in same database?

            • 3. Re: understanding a impdp(remap_tablespace)
              sb92075

              >1) So the remap_tablespace is used to move the objects from one  tablespace to other tablespace in different database?

              YES

               

              >2) If the source tablespace(SOUTHERN_TB) is deleted then there wont be any objects.In this case can i use remap_tablespace to move the objects to newly created tablespace in same database?

               

              you can not DROP TABLESPACE when any object still resides in the tablespace

              • 4. Re: understanding a impdp(remap_tablespace)
                Kamesh

                Dear Sir,

                 

                1) Let us assume if i accidentally deleted my SOUTHERN_TB tablespace which has 3 tables namely southern1,southern2 and southern3.

                 

                Before deleting the tablespace i took bthe export backup of the tablespace southern_tb.

                 

                Do you still say it is not possible to restore the 3 tables using remap_tablespace to different tablespace in same database?

                 

                Message was edited by: Kamesh

                • 5. Re: understanding a impdp(remap_tablespace)
                  sb92075

                  unwilling or incapable to Read The Fine Manual?

                   

                  Contents

                  • 6. Re: understanding a impdp(remap_tablespace)
                    Dean Gagne-Oracle

                    Kamesh,

                     

                    You can restore the tablespace or you can remap it.  If the objects that you are importing already exist (anyplace in the database) (same tablespace or different tablespace) Data Pump's default behavior is to skip these objects.  You can use

                     

                    table_exists_aciton=replace

                     

                    to drop an existing table and then import the table from the dumpfile.  If you have a remap_tablespace on this import job, then your existing table in tablespace southern_tb will be dropped and a new table will be created in tablespace kamesh.

                     

                    Hope this helps.

                     

                    Dean

                    • 7. Re: understanding a impdp(remap_tablespace)
                      Kamesh

                      Thanks Dean.

                      i was trying to do the import asnd i am getting the below error. Do you know what i am missing here ?

                       

                      C:\Users\Pranav>impdp directory=Datapump_testing dumpfile=SOUTHERN_TB.DMP schema

                      s=southern remap_schema=southern:kamesh REMAP_TABLESPACE=SOU

                      THERN_TB:KAMESH

                       

                       

                      Import: Release 11.2.0.1.0 - Production on Tue Sep 3 19:27:17 2013

                       

                       

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

                       

                       

                      Username: sys as sysdba

                      Password:

                       

                       

                      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

                      Production

                      With the Partitioning, OLAP, Data Mining and Real Application Testing options

                      ORA-31655: no data or metadata objects selected for job

                      ORA-39039: Schema expression "IN (SELECT object_name FROM "SYS"."EXPORT_ORA" WHE

                      RE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas

                      .

                      Master table "SYS"."EXPORT_ORA" successfully loaded/unloaded

                      Starting "SYS"."EXPORT_ORA":  sys/******** AS SYSDBA directory=Datapump_testing

                      dumpfile=SOUTHERN_TB.DMP schemas=southern job_name=export_ora remap_schema=south

                      ern:kamesh REMAP_TABLESPACE=SOUTHERN_TB:KAMESH

                      Job "SYS"."EXPORT_ORA" successfully completed at 19:27:25

                      • 8. Re: understanding a impdp(remap_tablespace)
                        DK2010

                        Hi,

                         

                        command should be  like remove schemas clause

                         

                        impdp directory=Datapump_testing dumpfile=SOUTHERN_TB.DMP  remap_schema=southern:kamesh REMAP_TABLESPACE=SOUTHERN_TB:KAMESH

                        • 9. Re: understanding a impdp(remap_tablespace)
                          Kamesh

                          Thanks sb92075 , DeanGagne and DK2010. You guys made my day.