5 Replies Latest reply: Jan 30, 2013 12:50 AM by 588481 RSS

    impdp and transform=segment_attributes to reclaime space from the tablespac

    588481
      Hi all,

      I hope somebody can help me out here.
      I have a production database and a couple of copies of that for developing and testing.
      Recently there have been done some cleanups on several tables where innitially over 50 milj records were stored, 12 milj got removed, placed back and then 34 milj got removed.
      Of course looking in the enterprise manager it tells me there are gigbytes of reclaimable space in my table spaces.
      The tables with data I have stored in a tablespace called EDB and the indexes created for these tables are stored in the tablespace EDB_IDX.
      With the Enterprise Manager I can easly use the SHRINK option to reclaim the space on EDB_IDX but this does not work on the EDB tablespace.

      I have been doing some digging around and tried this out on my development server. I had to refresh the data of one of those schemas anyway.
      So what i did is include the option 'transform=segment_attributes:n'. When the import of the tables with data was completed I could see the usage of my tablespace was even 20Gb smaller then it was before so thus far perfectly good. But then it starts creating the indexes and these were created in my EDB tablspace instead of the EDB_IDX tablespace where I want them to go.

      Logical because the transform option I used with the data pump import strips all information.

      Is there a way where I can accomplish the import where the reclaimable space is taken into account but preserves my tablespace destination ?

      I'm on 11g by the way. (11.2.0.3 Enterprise Edition)
      The production database is on its own database server and schema.
      The development environments are on one seperate oracle database server each with a schema per environment.

      Any help is highly appreciated.



      Regards, Luc
        • 1. Re: impdp and transform=segment_attributes to reclaime space from the tablespac
          Richard Harrison .
          Hi Luc,
          try:

          TRANSFORM= SEGMENT_ATTRIBUTES:n:table

          There is extended syntax to just do tables or just do indexes - the default is both.

          Cheers,
          Harry
          • 2. Re: impdp and transform=segment_attributes to reclaime space from the tablespac
            588481
            Hi Harry,

            Thanks for the advice.
            I started the import again with your suggestion.
            The indexes are created in the correct tablespace now, but of course with all the attributes it got exported.
            Probably not that of an issue as the SHRINK functionality works fine on my indexes.
            I have to figure out what is going on with the EDB tablespace holding my tables though because the space used now is even bigger then from the usage where I got it exported from.
            This is weird to say the least.

            I have to dive into that in the morning, I call it a day here for now, 6pm and dinner time
            • 3. Re: impdp and transform=segment_attributes to reclaime space from the tablespac
              Richard Harrison .
              Hi Luc,
              Could be that the original tables have some sort of compression in the source system and this is being stripped off when you remove the segment attributes from the tables? If you're using advanced compression you can just set that at the tablespace level and all will be fine - if it's just old style compression then it may be a bit more fiddly to deal with.

              Cheers,
              Harry
              • 4. Re: impdp and transform=segment_attributes to reclaime space from the tablespac
                User286067
                Luc De Backer wrote:
                Hi Harry,

                Thanks for the advice. I started the import again with your suggestion. The indexes are created in the correct tablespace now, but of course with all the attributes it got exported. Probably not that of an issue as the SHRINK functionality works fine on my indexes.
                did that shrink operation on indexes save you any space ?
                I have to figure out what is going on with the EDB tablespace holding my tables though because the space used now is even bigger then from the usage where I got it exported from.
                This is weird to say the least. I have to dive into that in the morning, I call it a day here for now, 6pm and dinner time
                I guess If you ran impdp into target db then running 'shrink' should be a moot point since you are essentially re-creating table and index (thus removing almost all empty space). If you want to really test you need a db which is identical to prod, so you will need to use rman and create a db using a hotbackup. This will create an identical copy and then you can test your shrink operation.

                Also check tablespace parameters (system managed or autoallocate etc), blocksize and such between prod and non prod databases when you are checking about size difference.

                Raj
                • 5. Re: impdp and transform=segment_attributes to reclaime space from the tablespac
                  588481
                  Hi all,

                  First off, thx for the reactions and suggestions.
                  One thing we have made sure of here, is that the database and server installations themselfs are completely identical. (well, expect for the amount of cpu's available on the development server due to Oracle license costs)
                  There has not been done anything with (advanced) compression at all and Segment Management has been set to AUTO for the tablespaces. Normally we don't have to take a look for reclaimable spaces that much even but as mentioned the past few weeks there have been done some heavy deleting, inserting and deleting of records in several huge tables (well, they are huge for our environment ;) ) and so I have seen an extensive growth on my two tablespaces and messages from within the Enterprise Manager there are gigbytes of reclaimable space.

                  To answer the question if SHRINK on the indexes works to free up space in the tablespace I use for the Indexes (made the split between tables and indexes because of household purposes, def not for performance because that's a myth) YES, the SHRINK frees space in the tablespace for the indexes.

                  However when I look now at the import done where the segment_attributes:n:table is set, it claims more space again than from the source database. I think, and correct me if I'm wrong, it is due to the fact that on the development server there are more schemas imported using the same tablespaces and then the attributes can't be used 'one on one' as they are set in the source database.

                  It is a shame that you can't set a default tablespace for an object type within a schema.
                  I wonder though if not something else can be achieved by splitting up the import between tables and indexes by for example first do the import with the option exlcude=index and afterwards only import the indexes. After all they are not really imported but re-created based on the index information (including segment attributes) inside the dumpfile. I might have to fingle with that a bit.

                  Anyway, my end result is that the EDB tablespace went from 40Gb towards 28,5Gb. My EDB_IDX went up from 34,5Gb to 40Gb, where I'm almost sure I should be able to get as low as 32Gb for the indexes.

                  So, in short I achieved my goal to free up space in the tablespace for the tables and tabledata with the 'transform=segemnt_attributes:n:table' option. So thank you very much all. :)


                  Regards, Luc