I am putting a hypothetical question, let's assume that i have a tablespace size of 3GB, and i have one table associated with the same of size 2GB, so we are only left with 1GB of tablespace space, i want to perform reorg on this tablespace, however, to reorg a table of size 2GB we would need equal amount of space to reorg it either in the the same tablespace or a temporary tablespace.
Now i have a temporary tablespace of size 8k and no of total pages are 1, i would also like to add one more condition that i am left with no filesystem space and i am not allowed to request to increase it, so alter tablespace is out of question, under all these conditions in place, would i be able to reorg it and if yes, how?
the same question was asked to me and i was told that with all these restrictions in place i can still reorg it..i want to ask how and why?
I am eager to hear the answer, any help would be appreciated.
Since this was a hypothetical condition to begin with, i did not took in to account all these factors.
There is a default bufferpool that this tablespace is using, and this is not a partitioned table, what would be the minium size of the index in this scenario for reorg to work ( if it will)and how does it effect, since indexes are on a different tablespace?
If we speak on classic reorg, then indexes must be rebuilt at the end of reorg. This means that you need system temporary unless you have SORTHEAP large enough to hold all index keys.
So, usually you have to have a system temporary table space for reorgs. If you don't have free disk space for it, you can create a RAM disk, for example, and a system temporary table space large enough in it.