HELP-need gurus-Oracle Support conflicting answers

Make a long story short -
Oracle 7.3.4.4 on AIX 4.3.1 archive log mode I added a datafile to a 12G tablespace to allow for growth.. I then realized I had named it wrong.
I did an alter database datafile 'wrongfilename' offline drop. I then removed the datafile from the os. I then noticed it still showed up in the dictionary as available at 100MB.
This scared me. ( I thought it would leave the dictionary) I stopped the backup from running for fear the database would not come up
without the datafile.
My first call to Oracle support indicated that this was an issue that could cause
corruption and that the only way to remove it was to drop and re-create the
tablespace. This was/is not an option. It would have to go to tape somewhere and would take a lot of involement to accomplish. My second call to Oracle support got a different analyst who said it wouldn't matter
at all. The database would know the file was empty and not to worry about it.
He told me to shutdown and startup to prove it. He was right. Although the file
shows up in a backup controlfile to trace, the database starts up fine without the file
there. He again said that it would hang out in the dictionary until some future re-org
but would not cause any harm. These answers directly conflict each other.
Which representative was right? Clearly the second one was right about the database not being bothered by it.. but getting 2 different answers makes me queesy.
I have a few fears -
1) Since the dba_data_files view still shows this file as available and 100MB even
though it is not there, will the tablespace attempt to extend into it in the future and
cause a nasty error.
2) Although the database appears to be fine for the time being, a copy of the database with the "SET NAME" create controlfile command is used every morning
on a different box. Will this work? I will know within 10 hours.

This also leaves me with a few questions - 1) Is there no way to remove a datafile safely without re-creating the tablespace?
Oracle was pretty stubborn about this, and said yes, essentially, when you create
a datafile you are pretty much stuck with it, but I get the impression from the Sever
SQL reference manual that the behavior of offline drop has something to do with
whether the database is in archive log mode or not. I will do some experiments on a
test database tomorrow, but would appreciate any feedback anyhow.. 2) Is there some sort of clever trick that can be done - like a) taking the database
out of archive log mode long enough to get rid of this file. b) resizing it to 0 c)
adding it back d) anything at all !

Also - I find it hard to believe that Oracle support, who can walk you through re-creating binary control files has absolutely no tricks up their sleeve for getting
a datafile out of the dictionary.. I mean.. they must have clever little hacks sitting all
over their office...

I'll be honest.. my experience with Oracle Support is like flipping a coin. Occassionally, you get someone very sharp, and often you don't. I find often the
best approach is to walk in with as much information as possible, sometimes more
than they have on the tip of their brain, and then they generally put you on hold and
get someone more clever.. in light of this.. I am curious if anyone knows of a nasty
way to get rid of the file.. Dollar views, etc., I don't know.. I would NEVER do it
without a blessing since this is an important database.. but I am curious.. maybe
I can nudge support into going for it..

Thanks,
Dc. Received on Tue Mar 28 2000 - 00:00:00 CST