Re: Filesystem space not freed when drop tablespace

On 23 Mar 2006 05:12:46 -0800, schonlinner_at_yahoo.com wrote:

>Hi,
>
>we have a unix server running HP-UX hp-adv-2 B.11.11 U 9000/800.
>4 processors, Oracle 10.1.0.4.0.
>
>When I do a "drop tablespace MY_TBL_10M including contents and
>datafiles", then it sometimes happens that the space in the file
>system does not get freed. Sometimes it lasts a few minutes,
>sometimes one can wait forever.
>
>Ok, this basically means that Oracle still has a valid open
>file pointer to the datafile(s) and thus Unix cannot free
>the space.
>
>Well, but why doesn't Oracle release it? We waited about 18
>hours (expecting that in some idle system phase over night Oracle
>cleans up and closes file pointers), but nothing happened.
>We then finally restarted the database to get the space in the
>file system freed.
>
>Is there anything which we can do to monitor which session
>or Oracle process still holds a reference to a dropped
>datafile in a dropped tablespace?
>
>Restarting the database is a very crude solution and not
>recommended to us...
>
>Best regards,
> Alex
>

And BTW : You should of course not drop the datafile when examining with fuser. Next time you drop a tablespace, keep the datafile with

"drop tablespace MY_TBL_10M including contents"

And then apply fuser to see which processes still hold the file.

• Kenneth Koenraadt