3 lessons I learned today, the hard wayPosted: August 30, 2007
Sometimes you have a difficult day. I heard that turning difficult days into lessons and viewing them as an opportunity for growth and improvement should help. So here is what I’ve learned today:
1) Always, always work from spec. That is, a detailed description of what the project should do, and what it shouldn’t. This description should be accepted by the customer representative. You will get in trouble if you skip this, even if it is a tiny and obvious project. I got a project to write a utility that verifies that the customer gets his data dump for each of his 100+ schema every day and alerts us if it didn’t. Sounds obvious, right? So I worked without spec, only to learn later that the customer expected the utility to verify that the file was created before 3am. Not a huge thing to fix, but its still better to get this information before it becomes a production issue
2) Keep an updated list of all the development projects in the department, who requested the project, important dependencies and who is responsible for it. Six month ago we upgraded a bunch of customers to a new version of the product and moved them to a new DB. The old schema were deleted last week. Today the application owner found out that an important procedure that the customer used in the old version was never copied to the new DB. None of the DBAs knew that the procedure existed, so it was ignored in the migration procedure. Now the DBAs can recover the procedure from backups, but the application owner is still looking for a developer than will make the procedure match the new application version. You can prevent this sort of confusion by having a list of projects, their customers and a developer who should maintain each.
3) When you type:
create global temporary table as select from.... don’t be surprised if you get an empty table. You need to type
create global temporary table on commit preserve rows as select from.... Create as select has an implicit commit in there.
Also, The following query will fail with “invalid number” error:
select 'number of header blocks in file ' || file_name || ' is ' || blocks-user_blocks from dba_data_files
but this will work:
select 'number of header blocks in file ' || file_name || ' is ' || (blocks-user_blocks) from dba_data_files
Apparently || operator has precedence over + operator which makes the first version meaningless.
Hopefully, I’ll never make any of these mistakes again.