Monday, January 31, 2011

DBMS_FILE_TRANFER.COPY_FILE and locking

I was working on copying an RMAN backup from an NFS mount into ASM to do a restore of a database.. I figured.. A great opportunity to use DBMS_FILE_TRANSER to copy it in !!
I copied the first file into ASM, and deleted off the mount. I then copied over my next file (I only had room for 1). BAM. I'm out of space. I look at the file system, and the space is used up (with a 'df -k'), but I couldn't file the file using up the space. I thought.. maybe dbms_file_transfer doesn't release the file handle from the OS? I shut down the database, and my space returns.

I haven't had time to play with it, but beware that using DBMS_FILE_TRANSFER holds the file open, so deleting it doesn't remove the space. I'm not sure how to release the handle.