Re: Filesystem space not freed when drop tablespace

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 24 Mar 2006 04:45:05 GMT
Message-Id: <pan.2006.03.24.04.45.02.517548@sbcglobal.net>

On Thu, 23 Mar 2006 17:49:55 +0000, wrote:

> 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
>>

> 
> Hi Alex,
> 
> Look at the "fuser" utility.
> 
> The command
> 
> "fuser file_in_question.dbf"
> 
>  will list the processes currently accessing the file. Most likely you
> will find an Oracle background process among them, if any.
> 
> - Kenneth Koenraadt

The "fuser" command doesn't work without the file.

lsof +D /directory/above/file_in_question.dbf will show what process is using which inode:

[root_at_medo ~]# lsof +D /opt/apache
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME

httpd   3015   root  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3015   root  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3015   root  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3015   root  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3015   root    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3015   root    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3015   root    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log
httpd   3048 nobody  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3048 nobody  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3048 nobody  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3048 nobody  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3048 nobody    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3048 nobody    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3048 nobody    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log
httpd   3049 nobody  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3049 nobody  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3049 nobody  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3049 nobody  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3049 nobody    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3049 nobody    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3049 nobody    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log
httpd   3050 nobody  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3050 nobody  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3050 nobody  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3050 nobody  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3050 nobody    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3050 nobody    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3050 nobody    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log
httpd   3051 nobody  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3051 nobody  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3051 nobody  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3051 nobody  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3051 nobody    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3051 nobody    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3051 nobody    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log
httpd   3052 nobody  txt    REG    3,7 1401258 194065 /opt/apache/bin/httpd
httpd   3052 nobody  mem    REG    3,7 9802135 194068 /opt/apache/modules/libphp5.so
httpd   3052 nobody  mem    REG    3,7  425774 192609 /opt/apache/lib/libapr-0.so.0.9.6
httpd   3052 nobody  mem    REG    3,7  281729 192613 /opt/apache/lib/libaprutil-0.so.0.9.6
httpd   3052 nobody    2w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3052 nobody    6w   REG    3,7   14604 180248 /opt/apache/logs/error_loghttpd   3052 nobody    7w   REG    3,7  297921 127012 /opt/apache/logs/access_log

[root_at_medo ~]#

Of course, lsof needs to be installed on the system as it is usually not a part of the OS. The "fuser" command should only be used for describing the DBA feelings for the end users.

-- 
http://www.mgogala.com

Received on Thu Mar 23 2006 - 22:45:05 CST