5 Replies Latest reply: Sep 10, 2013 8:26 AM by Dean Gagne RSS

    EXPDP excluding table spaces

    972590

      Hi All,

       

      I am using EXPDP to export a schema (Oracle 11g R2), and I need to exclude all the tablespaces that the schema is using. I have seen exluding Oracle objects like functions, tables, packages, indexes...etc. But I have not seen excluding tablespaces.Iis it possible to exclude tablespaces while creating the export dump? Please share your thoughts.

       

      Thanks

        • 1. Re: EXPDP excluding table spaces
          TSharma-Oracle

          I don't think you can exclude tablespace. Its a logical backup. It will contain the tablespace definition only. What is your purpose of excluding tablespaces? what are you trying to achieve?You can also use REMAP_TABLESPACE parameter while importing the backup.

          • 2. Re: EXPDP excluding table spaces
            972590

            Thanks TSharma for the reply.  The reason why I wanted to exlcude the tablespaces is, the source scheam has 10 tablespaces, and I wanted to import this schemas data into one tablespace only in the target schema. The target schema is a test schema. So I do not wnat to create 10 tablespaces.

             

            Thanks

            • 3. Re: EXPDP excluding table spaces
              TSharma-Oracle

              That is why I said you can use REMAP_TABLESPACE parameter while importing data. Read on this parameter.

              • 4. Re: EXPDP excluding table spaces
                DK2010

                Hi,

                 

                You can simply use this like REMAP_TABLESPACE=SYSTEM:TEST,USERS:TEST,A_TS:TEST,...

                Mapping is  SYSTEM ==>TEST

                                  USERS==>TEST

                                   A_TS ==>TEST

                 

                HTH

                • 5. Re: EXPDP excluding table spaces
                  Dean Gagne

                  YES you can EXCLUDE TABLESPACES!!!

                   

                  What you need to do is make sure the tablespaces are in the Data Pump job you are running.  Let me explain...

                   

                  When you do a table mode export TABLES=HR.EMPLOYEES, you are exporting objects related to this table.  Although this table lives in the tablespace, the tablespace is not exported. The tablespace is not dependent on the table.  Indexes, constraints, statistics, are, so those can be excluded or included.

                   

                  When you do a schema mode export, anything owned by that schema is exported.  Tablespaces are not owned by a schema, so they are not exported.  So... you can't exclude tablespaces from a schema mode export since they are not part of that type of job.

                   

                  When you do a FULL=Y export, you are exporting the database.  Tablespaces are part of the database and they are exported and imported.  In this type of job, you can exclude or include tablespaces.

                   

                  Hope this helps.

                   

                  Dean