Temporary Tablespaces - a misadventure

Looking at the config of the databases in my new position, early on I decided that the temporary tablespace of our production system is WAY oversized for the database and queries it's serving.  We never use more than about 15% of it, even under heavy load.

One evening, in the downtime/maintenance period, I decided to reduce it.  My first attempt at reducing the datafile size with an "alter database tempfile 'x' resize nG;" failed with an error:

ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Makes sense.  From there, I followed a process I muddled together from some googling which involved:

1. Add another temporary tablespace
     CREATE TEMPORARY TABLESPACE TEMP2 SIZE nG
2. Set this to be the default temporary tablespace
     ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
3. Remove the initial temporary tablespace
     DROP TABLESPACE TEMP
4. Rename the new temporary tablespace
     ALTER TABLESPACE TEMP2 RENAME TO TEMP

This process seemed reasonable to me.  However, it all failed at step 3.  The drop tablespace command sat for about 15 minutes with no obvious activity in top, iotop or the alert log.  I took the decision to cancel it, and checked that everything still looked OK.  It did.  The tablespace was still there, the datafile was online, everything looked happy.  I left things as they were and locked up for the night.

The next morning... the alerts... oh my.  So many alerts.  

It looks like, despite it being a maintenance period, there was a process running that I wasn't aware of.  That process, responsible for building a couple of reference tables, had ended up hanging with locks on a couple of key tables until 3am or thereabouts, when it bombed out with a "Snapshot too old" error (i.e. - ran out of undo).

There's a few things I've learned from this...

• There's a better way to do what I was trying to do (e.g. - http://www.oracle-base.com/articles/11g/temporary-tablespace-enhancements-11gr1.php):
   alter tablespace TEMP shrink space keep nG;
• If I'd have checked that there were no sessions using temporary tablespace and no unexpected users connected, this may all have worked first time - check:
   V$SORT_USAGE and V$SORT_SEGMENT
• Is it really worth it? Is saving 10G/20G/40G (which may just get used up again in the future) really worthwhile?

It also reinforced my belief that we really need some way of visualising the ridiculous crontabs that we have on the database server.  I thought I had it understood, but there's so many jobs scheduled with varying repeating schedules, it's hard to see what's running when with any clarity.