Why is it that the database always falls over when you're trying something new
for the first time? Today I was working with OpenACS on a job where we want
to relate people to each other. OpenACS has a few tables for handling
relationships and relationship types, so I had created a new type in SQL*Plus
and was going into the admin pages to make sure the type was visible.
KAPOW!
Server error. The web server had lost its database connection, which of
course must be some other idiot developer on the system. The web server
can't do much without the database, so I let the developers know and
bounced it.
Now, back to my admin page...
KERSPLAT!
Server error. Hmmm. I really should have checked the error log. Turns
out we're getting an ORA-01114 IO error writing block to file string (block # string),
which no developer ever wants to see because it means Mr. Database is
having an off-day. So I went off and restarted the web server again,
and just to make sure it was a pure Oracle error, I found the query
and ran it from Toad.
WHAPZING!
ORA-01114. Fair enough - it looks like Oracle needs a little TLC. Looking
at the temp tablespace using Enterprise Manager (a tool for quiche eaters
if ever there was one) I see that it has grown to 8Gb and is 99.99% full.
The tablespace is set to autoextend and hasn't reached its maximum size,
so maybe something is amiss with the datafile? The temp dbf file is on
a partition mounted at /data1.
Logged in to the Linux box where Oracle lives, a df -h tells me /data1 is full.
Here we have a few options:
- Add another datafile to the temporary tablespace
- Drop the temporary tablespace and recreate it a lot smaller
- Free up some disk space in /data1
The first option is easy wasteful, since the 8Gb of space currently being
used is most likely stale. It may or may not be cleared out when Oracle
is restarted, but unless naughty people have been deliberately putting
objects into the temp tablespace, you can bet your granny you could
live without it being quite so big.
The second option is the proper thing to do. Unfortunately that database
is in use by lots of people and dropping a big tablespace can take a long
time. While these people would love the excuse for an extended tea break,
the PHBs who pay their and my wages wouldn't!
Luckily for me there were a few CD images lying around in /data1 and
deleting these allowed me to free up 3Gb. As soon as I did Mr. Oracle
immediately perked up and resumed normal service.
Of course when I got into my admin page my relationship type is still
missing, most probably because I thought I could get away with leaving
out a trigger... but don't get me started on triggers!!
