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