Had an interesting experience today with the "alter database datafile
offline drop" command, here is what happened:
There was three datafiles at 50M,50M and 100M for the temp tablespace, we
decided to consolidate the three in to one 300M file, so resized one of the
files to 300M, then did an offline drop on the remaining two.
This is where it got "interesting", prior to this I had a TOAD session open
and was doing extensive viewing of the stored procedures, packages and
functions, as well as some general queries. After dropping the two temp
datafiles I went back in to TOAD to look at one of the packages and got an
error saying that it was no longer available in file 5, along with all of
the other packages and one of the stored procedures. Closed toad and opened
again, same error, so it appeared that the stored procedures were in the
temp tablespace and were lost when it one of the datafiles was dropped,
bounced the database - same thing. Ended up doing a "recover tablespace
temporary_data" after taking the datafiles offline - this fixed the problem.
Still not sure what happened, it appears like the procedures were stored in
the temp tablespace while we were viewing them, after recovering the
tablespace no objects appeared in the temp tablespace, but I am very
hesitant to drop the two unneeded datafiles now.
Anyone else had an experience like this?
