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

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 28 Jul 2003 04:11:12 -0700
Message-ID: <1a75df45.0307280311.58a6a78f@posting.google.com>

wizofoz2k_at_yahoo.com.au (Nuno Souto) wrote
>
> Disagree. As the OP said: he's dropped the tablespace
> and wants to lose the file at OS level. Why the heck is
> Windows still hanging on to it even though Oracle has obviously
> closed the file unit?

I said that this could point to Oracle only releasing the file handle when the client actually terminates (i.e. the session closes on the v$session side).

> As soon as a process does fclose(), Windows should release
> any file locks that process might have held on the file.
> Yet it doesn't: you can prove that with a little C program
> in command line mode in Windows.

Well, you almost had me convinced. :-)

Until I wrote the following very quick and dirty Delphi console app.. which proves that as soon as the file handle is released, I can delete the file, even though the application has not yet terminated.

Delphi 6. Windows 2000 SP2.

---

program FShareDemo;

{$APPTYPE CONSOLE} uses
  Windows,
  SysUtils;

var
  h : Thandle;
  fname : array[0..255] of char;
begin
  if ParamCount <> 1 then halt; // command line parameter is the filename
  StrPCopy( fname, ParamStr(1) ); // putting it into a zero terminated string

  WriteLn('Opening file '+ParamStr(1) );   // opening the file as non-shareable
  h := CreateFile( fname, GENERIC_READ or GENERIC_WRITE, 0,

              nil, CREATE_ALWAYS, FILE_ATTRIBUTE_NORMAL,
              FILE_FLAG_SEQUENTIAL_SCAN );

  Write('Press any key to close the file....');   ReadLn;
  // I can not delete the file from another cmd shell   // (it says file is in use by another process)

  WriteLn('Closing file '+ParamStr(1) );   CloseHandle(h);
  // I now can delete the file from another cmd shell   // (note that the program has not yet terminated)

  Write('Press any key to terminate....');   ReadLn;
  WriteLn('Done');
end.

---

How does the C console app do it to disprove this?

> This is different from the situation you reported in Unix
> whereby a file can be deleted but is not really until
> the process last locking it has finished or closed the
> file unit. That is the correct behaviour, abstracting
> of course security checks.

Agree.

--
Billy
PS. Sheez, you almost got as badly trashed as us on Saturday by the
All Blacks... Are they just that good or are our teams that bad? :-)

Received on Mon Jul 28 2003 - 06:11:12 CDT