4 Replies Latest reply: Dec 30, 2009 4:18 PM by Dean Gagne-Oracle RSS

    Can oracle 9iR2 export a schema but exclude a tablespace?

    FireFox88
      Hi

      I have a question. Is it possible for me to export a schema but to exclude one of the table space within it?

      If yes, what is the command look like?

      If no, what is the workaround?
        • 1. Re: Can oracle 9iR2 export a schema but exclude a tablespace?
          Deepak_DBA
          hi,

          >
          I have a question. Is it possible for me to export a schema but to **exclude one of the table space within it**?
          what do you mean by one of the tablespace within it.. does a schema contain a tablespace? or a schema is assigned toa a tablespace..


          regards,
          Deepak
          • 2. Re: Can oracle 9iR2 export a schema but exclude a tablespace?
            Dean Gagne-Oracle
            Hi,

            Oracle 9 will force you to use exp/imp and this limits what you can do. In both exp/imp and expdp/impdp (datapump), you can't exclude a tablespace from a schema export.

            If you are trying to exclude the tablespace definition, then this is never exported in a schema mode export. The tablespace definition is only exported in a full=y.

            If you are trying to exclude all of the tables in a tablespace, then I don't think you can do that with exp/imp. If you were on 10 then you could use datapump and you could use the exclude tables and then give it a list of tables to exclude.

            If there is someway you can take the tablespace offline then exp won't be able to see the tables in that tablespace. I'm not sure if exp will give you an error, but it's worth a try.

            Dean
            • 3. Re: Can oracle 9iR2 export a schema but exclude a tablespace?
              Lubiez Jean-Valentin
              Hello,


              If I well understood, you want to export the Tables / Indexes of a schema except the ones located in a specific
              Tablespace.

              For the Tables it's rather easy.

              So assume the Tables of your Schema are located on 2 Tablespaces A and B and you want to Export
              the Tables of your Schema located on the Tablespace A only.

              Then, you can query the database so as to find the list of Tables located on the Tablespace A as follows:
              select segment_name
              from dba_segments
              where owner = '{color:red}YOUR_SCHEMA{color}'
              and segment_type = 'TABLE'
              and tablespace_name = 'A';
              And you can use a Parameter file and Export in TABLE mode with the list of Tables (selected by the query above)
              as bellows:
              TABLES=(
              {color:Red}YOUR_SCHEMA{color}.{color:red}TABLE_NAME_1{color},
              ...
              )
              But for the Indexes it's more difficult, because if you want a functional Schema you'll have to import
              all the Index dependent of the Tables, wherever the indexes are located.

              More over, for the other type of object you'll have to export the structure (ROWS=N) in Schema mode.

              Please find enclosed, the parameter for the Export utility in 9.2:

              [http://download.oracle.com/docs/cd/B10501_01/server.920/a96652.pdf]

              Hope it can help.
              Best regards,
              Jean-Valentin
              • 4. Re: Can oracle 9iR2 export a schema but exclude a tablespace?
                Dean Gagne-Oracle
                The problem with the previous post is that a schema mode export and a table mode export are 2 different exports. Schema exports get everything owned by the schema including packages, triggers, materialized_views, etc. A table mode export will only get things directly related to a table like tables, indexes, index stats, table stats, etc. So if a schema mode export is needed with the exception of a few tables that are all in the same tablespace. I think the only way to do this would be to take the tablespace offline. If all you need are objects related to tables, then the previous post should work fine.

                Dean