Re: 'drop tablespace' produces a file/table lock??

jared_at_hwai.com (Jared) wrote:

> Just had an interesting problem and wondered if anyone knew the why of
> it - I just dropped a tablespace. Went to delete the file at the OS
> level (I'm in 8.1.7.4.1 on Win2K) and Windoze wouldn't let me - said
> there was a sharing violation. I've only done this a gazillion times
> in *nix and other OS'. Closing the SQL*Plus session that I had used
> to drop the tablespace released whatever lock there was; presumably
> ORACLE.EXE had a lock on the datafile until I exited SQL*Plus.

Not sure what you think the problem is Jared..?

IMO this is how it should work. If it does not on other operating systems.. well then that is more a flaw/problem in that o/s than what it is Windows.

Windows does not decide what to lock and not to lock (exluding system files like the kernel and swap for example).

Thus, it will not be locking a file UNLESS some process specifically stated that in its fileopen()/_fopen() call. Which means that file handle will be locked until the closefile()/_fclose() call.

What Unix does allow to do is for you to delete a "locked" file. Or use to in the old days when we still had 2GB max file systems.

What you deleted were the file name entry.. the i/o nodes allocated to that file was not released until after the lock has been released. Thus you could "delete" a massive file and do a df/bdf and still see no free space as a result.

> When I was a VAXcluster manager several decades and a marriage ago,
> one could create one's own message classes. Many had a 'WTF' class
> (one leaves it to the reader to parse out the acronym); and any error
> not otherwise trapped returned something like "WTF - what are you
> trying to do?" to the unfortunate user. This just seems like one of
> those situations.

Beg to differ. IMO Windows is working as what is expected (and it has borrowed heavily from VMS btw).

Unless you are refering to Oracle that has an inconsistent behaviour (compared to Unix) in the way it drops a datafile on Windows (which I doubt).

--
Billy