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

    EXPDP excluding table spaces


      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.



        • 1. Re: EXPDP excluding table spaces

          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

            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.



            • 3. Re: EXPDP excluding table spaces

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

              • 4. Re: EXPDP excluding table spaces



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

                Mapping is  SYSTEM ==>TEST


                                   A_TS ==>TEST



                • 5. Re: EXPDP excluding table spaces
                  Dean Gagne-Oracle

                  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.