Unable to free disk space after online moving of datafile by ALTER DATABASE MOVE statement on Linux

Many times I encountered into situation, when, after successful moving of large datafile to new place, amount of free space in old place is not increasing.

To resolve :

1. Find oracle processes continuing using deleted datafile. In my cases, it always were user' (not system') processes.

:> lsof | grep deleted_file_name_with_path

2. Find file descriptor number for deleted datafile of each process. It's also possible to verify file descriptor number via /proc filesystem.

3. Run gdb (GNU debugger) and close open file descriptor for deleted datafile.

4. Repeat above steps for each oracle process, holding deleted datafile.

5. Control free disk space on the filesystem.

Example :

1. Searching for PID and FD

:> lsof | grep delet

oracle_11 119147           oracle  258u      REG                8,3 286157651968   67109389 /u01/oradata/ofsaaprd/aaa_from_test.ora (deleted)

Here :
- 119147 - PID of the oracle process ;
- 258 - interesting file descriptor number

2. Run gdb

:> gdb -p 119147

3. Close FD in gdb

(gdb) call close (258)

If you get the error

'close' has unknown return type; cast the call to its declared return type

then try 

(gdb) call (size_t) close (258)

4. Quit gdb

(gdb) q

An example of it run in the command line  :

while read line ; do ls -l /proc/$line/fd | grep dele | awk '{print "call (size_t) close ("$9")\n"}' > /tmp/gdb.cmd ; echo "quit" >> /tmp/gdb.cmd ; gdb -b atch -x /tmp/gdb.cmd -p $line ; done < <(lsof | grep delet | grep path_pattern | awk '{print $2}' | sort -u )

Good Luck !!!