Important task completed - lessons learnt

Finally completed the important task early this week, the tight deadline didn't allow for much room for errors, but this year's job seems especially problematic. The documentation is quite outdated, and the scheduled timing of the jobs are way off. This affected with the planned execution, and the problem was exacerbated by the shorter working days due to Chinese New Year. Then the network was constantly disconnecting, so there was a need to constantly monitor and rerun the jobs. And it's my first time running the jobs, so needed a lot of help from users, colleagues and some trial and error.

Overall, I did learn some things:

1. Creating an index on database tables is not sufficient to ensure fast queries. Always check the execution plan to see if the index is being used, as sometimes overindexing will cause Oracle to use a full table scan instead.


2. Be careful of ending commands and statements in SQL Plus with "/" instead of ";". If a command such as "spool C:\file.txt" is ended with "/", the last run statement will be run again, which can be a problem.


3. If the rollback segment is insufficient, it may be possible to split the query into smaller batches to run and thus not trouble the DBA.


4. Always check if an object with the same name already exists when creating new objects in scripts and do not commit in the script to save some headaches.


5. Check if CPU usage in Win XP is a bottleneck by checking if the TaskManager constantly reports a usage of 100%.