How to shrink the filesystem space required for a bloated EBS instance (DB 11.2.0.3.0 / Solaris)
We have come to realize that a table WSH_EXCEPTIONS had nearly 1 billion rows in it. As a result the data space and index space used were close to 500 GB. The entire EBS footprint was 800 GB.
We found the culprit, being an "Import Shipping Locations" job, it was logging a million exceptions daily, and they were only warnings.
We disabled the exception logging and worked a ticket with MOS to truncate the table. The table is now tiny with 0 rows, 1MB of space.
We are using a managed service provider and they charge a premium for QoS1 disk space. We would like to return about 400 GB of the 500 GB back to the filesystem so we don't have to pay for that extra storage. We also need to do this on all our 7 DEV instances as well. We figured we would practice a process on three dev instances and then roll out to prod. Over time the prod to dev cloning process will fix the rest of them.