Ok, this is a situation you should never get into, but we were constrained by a 3rd party application.
10gR2 RAC database on 2 linux nodes.
We literally have over half a million objects, mostly small tables and views. As such our system tablespace is around 11Gb.
We will shortly be implementing an applocation upgrade to create one large partitioned table and migrate the data from the small tables to the large one. After which the small tables get dropped.
The problem will be is that we'll be stuck with huge system datafiles that potentially (probably) we will not be able to significantly reduce the size of.
Without resorting to an export import of the whole 500Gb database, does anyone have any ideas to reduce the size of the system tablespace?
Thanks for your time.