Hi ! I have an unusual problem here, let me expose it : each month my data goes to a tablespace specific for the current month : so, the data referring january/2005 are being inserted/updated/deleted in a tablespace named TS_200501, and when February comes, a tablespace TS_200502 will be made, and no more DMLs will occurs against January data. So, to save space (the datafiles for these things are very huge)in February 1st I want to shrink the datafiles for January’s tablespace.
The problem here is : sometimes, in this tablespace are made INSERTs for a table (say, TABLE A), and this occupies (say) extents 001 from 099 in a data file. Later, more huge INSERTs are made for table B, occupying extents 100 to 1500 (in the same data file, say). Later yet another small INSERT is made in TABLE A, occupying extents 1501 to 1502, and finally TABLE B data is deleted. You can see the problem now : due only to the extents 1051 & 1502, I can’t shrink these data file to fewer than the space occupied by the last extent, but there is a LARGE unused space in this data file, which I want to free back to disk, to use in others tablespaces. The common answer is "MOVE TABLE A", but the system is intensive OLTP (very few off-time allowed), and A is constantly being referenced, A have many indexes (they will be invalidated) , so MOVE is not an option. What I can do ?

Regards,


Chiappa