TechnoLust's Journal: Ask Da Dot: User Auditing of Oracle Databases?

Damn it Jim, I'm a Software Developer, not a DBA! Yes, over the course of my career, I've designed and implemented many a DB schema. I know my way around Oracle. I understand primary and foreign keys and indexes etc. But sometimes when dealing with unfamiliar tables and code, it's quicker to "futz around" with data in the tables until I get the desired results.

So, the other day I ran a 300+ line long update script (that was in our version managment system, and had been thoroughly tested) on production. This closes out the quarter in the custom sales software that I inherited when the Lead Architect quit. It ran for about 15 minutes, and created an output file of all the people it updated. Ok... so I send the National Director of Sales to the app to make sure everyone's goal performance was properly calculated. She called back and said, "Uhh.. where's [sales lady in the Worcester hotel]? She isn't there anymore." I looked and she's in the database, but she's not on the screen. I check the code... code should pull everyone in the table. I close SQL Nav and reopen and rerun the query and she's still there, I wasn't looking at cached data, this was live.

I went into the test db and saw that her goals from LAST YEAR would still show, so I copied the goals from last year over this years goal, changing only the value in Goal_Year to 2006. Still no dice. I kept mucking with it until I got it to show up, but I couldn't tell if it was what I'd JUST DONE, or something I'd done then undone, then changed later. I went into production and applied the least dangerous of the changes I made. It worked!

Has anyone out there seen a tool that will allow a user to "record" the changes they are making in the database without writing a SQL script for each one? I was doing a query and checking for things that didn't feel right. (Yeah, I actually have pretty good instincts for databases and many times I have no idea WHY that won't work, but I can tell right away it's going to cause problems.) So I'm making changes in the edit pane of SQL Navigator.

What would be even SWEETER is if someone could point me to some software that will look at 2 Oracle Instances and tell me the difference between them (or certain tables on them) with the ability to ignore certain columns (like the primary key, etc.). Often times something works in Production and doesn't in Test and I need to see what has happened break it.