This content has been marked as final. Show 5 replies
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
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.
Luc De Backer wrote:did that shrink operation on indexes save you any space ?
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.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.
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
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.
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. :)